How to add a new database user based on a windows user

Here’s another post based on my gripes against database administration tools and how difficult they are to use (and the fact that so many people seem to be ok with it).

Many people have multiple windows users on their PCs. Maybe one for administration and one for daily usage for example. That’s my setup, I have a “laptop\admin” user and a “laptop\me” user, and recently I was trying to create a new database on my local SQL Express 2008 installation while logged in with my “laptop\me” account. I was using SQL Server Management Studio to do this. Off course, it did not work. Here is the error I got.

“CREATE DATABASE permission denied in database ‘master’. (Microsoft SQL Server, Error: 262)”

What that means is that my “laptop\me” user does not have the permissions to create a new database on my SQL Express instance. OK, no worries, I get that. I should be able to start SSMS as “laptop\admin” and grant the said permissions to “laptop\me”. No such luck. Nothing is that simple in database land.

I’ll cut to the chase and list the steps you need to follow to make that happen.

– Start SSMS as an admin user (right click and choose “Run as Administrator”)

This is assuming that the admin user has the authorization to create a new login and grant the create database permission. To do the database login associated with that admin user will need to be in the “sysadmin” role. More baloney I know! Someone in the team felt that every developer needs to be exposed to the full permissioning machinery just to create a database. If you do not have a login that is in the sysadmin role for the SQL Server instance  you are working with, unfortunately you have to create one. Click here for a link to a blog post that can help you do that. The post is for SQL Server but works for SQL Express also.

– Create a login in the SQL Express instance

USE master
CREATE LOGIN [laptop\me] FROM WINDOWS

– Create a user for that login

USE master
CREATE USER mydatabaseuser FOR LOGIN [laptop\me]

– Grant permission to that newly created user

USE master
GRANT CREATE DATABASE TO mydatabaseuser

Too many steps in my opinion, but hey, how would we have “database guys” if this was easy!!

Advertisements
How to add a new database user based on a windows user

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s