My Name is
I'm also known as @writeline
I'm a Technical Evangelist @ Microsoft Germany and you can follow me on Twitter or visit my private blog stream.
Normally I always install SQL Management Studio for my development environment. But this time I just thought I would not need it. I installed my machine outside of the domain network. The SQL Server 2008 Express installation was done with a local administrator user which I enabled to have dbo admin rights. I have also given rights for the local administrators group in SQL Server Express. After joining the domain I added my domain account to the local adminstrators group. Everything looked good. So I just started SQLCMD and tried to attach the AdventureWorksLT database (from Codeplex). This error came up.
CREATE DATABASE permission denied in database ‘master’.
Hmm? Oh, I forgot that UAC stuff.
Why do I write this post? My first reaction was to search the internet for exactly the title of this blog post to find the CREATE LOGIN description in a step by step solution and was shocked by the results I retrieved. Mostly people talking in Forums about this just work around security with granting the hosting process unlimited rights like local system for the sql server engine or the IIS App Pool. My 9th hit on the list had a reasonable explanation on how to add a login but was using SQL Management Studio which I don’t want to use right now. The post is not a real step by step guide, it is just the steps needed in my scenario to enable the user to create, delete and work with a lot of temporary databases.
First open up SQLCMD and check with select user_name() if you are not logged in as guest (like in my case). I could omit the problem with elevating the SQLCMD process – as I’m in the local administrators group and would get dbo rights – but I wanted to give the user dedicated rights without elevating the whole process each time. Further, my main task with my domain user regarding this SQL Server instance is creating and working with a lot of databases.
The following steps show how to add a domain account using SQLCMD and give this user sysadmin rights.
In my case I had already my domain account in the local administrators group and assigned the local administrators group to the SQL Server dbo group during installation. To get the right token when starting SQLCMD I just had to start it elevated.
It depends on the database instance name how the server is specified. In the example above I use my local machine and the instance is called SQLEXPRESS.
First create a SQL Server login for your domain account.
CREATE LOGIN [your domain account] FROM WINDOWS;
your domain account has to be replaced with the domain account you want to add. For instance your domain is called darth and the user vader than it would be CREATE LOGIN [darth\vader] FROM WINDOWS; GO;
To check if the login was successfully created type in
SELECT NAME FROM SYS.SERVER_PRINCIPALS
and the domain user should appear in the result list.
You can retrieve a list of server roles using the sp_helpsrvrole procedure. So to add the user to the sysadmin server role group type the following (example is still domain darth user vader)
SP_ADDSRVROLEMEMBER ‘darth\vader’, ‘sysadmin’
Quit SQLCMD and open a non elevated instance with your domain account. Type in select user_name() and the result should be dbo.
I have to write that I don’t run any production environments on my machine. With giving my user sysadmin rights, even when not elevated, I open up attack surface through SQL Server. Therefore here the big disclaimer
Big disclaimer: In a production environment I do not recommend giving full dbo rights to a dedicated user account. Please ensure the login just gets the rights he really needs to do the work. Check also the site SQL Server Best Practices.