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!!
Code On A Boat 