create table permission denied in database 'master'

Question

I have installed the free version of sql server 2008 (sql server management studio express edition) on my PC. After installation I get the following error

create table permission denied in database 'master'

I tried reinstalling several times, but I keep getting the same error. When i checked

select user_account();

It showed that I was logged in as guest. How do I solve this? since I am not permitted to create a new login.

Have you removed yourself from the sysadmin role or something? Also what OS are you on? — Martin Smith, Sep 05 '11 at 22:17
Just read something that suggests running Sql Studio as administrator may help - on vista anyway - not sure if that helps — TheCodeKing, Sep 05 '11 at 22:42

score 11 · Answer 1 · edited Oct 27 '16 at 16:48

11

I've read the error can be caused by UAC (on older versions of SQL Server Express). Try right-clicking on SQL Studio and running as administrator.

If that doesn't work there's supposedly a fix here for the same issue. Probably worth a try.

Script to add the current user to the SQL Server 'sysadmin' role

edited Oct 27 '16 at 16:48

sdoxsee

4,451
1
25
60

answered Sep 05 '11 at 22:15

TheCodeKing

19,064
3
47
70

Sounds like the login the OP is using won't have `CREATE DATABASE` permissions. – Martin Smith Sep 05 '11 at 22:16
@TheCodeKing: It is not allowing me to create any new database. Also in the feature selection, I only get database engine services and shared features – user1212 Sep 05 '11 at 22:19
I ran into the same error and the script mentioned in this answer worked for me. Technically all one needs to do is two statements (see http://bit.ly/LN2Q6J): (1) `CREATE LOGIN [your domain account] FROM WINDOWS;` and (2) `SP_ADDSRVROLEMEMBER ‘your domain account’, ‘sysadmin’;`. I could do the first, but not the second; the reason was revealed from BOL: "Requires membership in the role to which the new member is being added." Catch 22. The magic needed, as done by the script, is to restart the SQL service in maintenance mode to add the role, then return to normal afterwards. – Michael Sorens Jun 22 '12 at 19:55
link has changed and can currently be found at https://gist.github.com/hugodahl/8b34cbacf7bcd491b116 (according to http://www.hrzdata.com/node/57) – sdoxsee Oct 27 '16 at 15:17
when installing sql server, did you add your current user as an admin to the server? – DForck42 Oct 27 '16 at 15:43

score 4 · Answer 2 · answered Nov 08 '12 at 15:49

4

You should use sp_addsrvrolemember to add your user into role 'sysadmin'. Here is link that helped me to solve this problem: http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/76fc84f9-437c-4e71-ba3d-3c9ae794a7c4/

answered Nov 08 '12 at 15:49

Oleksii Iaroshchuk

85
5

score 4 · Answer 3 · answered Dec 09 '18 at 23:05

4

If your table actually exists in a different database (not master), you will need to switch to that database. A GUI option to change the database reference is shown below.

answered Dec 09 '18 at 23:05

Gwen Au

859
9
10

score 3 · Answer 4 · answered Feb 28 '14 at 14:36

3

I had the sam eproblem even though I was logged in as master. I was showing logged in as "guest", when I used 'select user_name();'. I used 'USE ,Database>' clause before script an dit really worked. I hope this works for some of you too.

answered Feb 28 '14 at 14:36

user2908745

41
2

1

Another way is to check the "Available Databases" dropdown in the upper left corner when you are focused in on a query to pick the right DB. I was hitting master when I had permissions for a different DB. See here: http://imgur.com/a/mSFJe – Jeff L Aug 30 '17 at 21:31
Thanks [Jeff](https://stackoverflow.com/users/3828302/jeff-l), that was very helpful. I'm going to post the visual in the answer to make it stand out a bit more. Cheers. – Gwen Au Dec 09 '18 at 22:40

score 2 · Answer 5 · answered Oct 23 '12 at 12:20

2

select USER_NAME() execute this query,if you find the username as guest then just close the sql server..

2.Then go to start menu right click the sql server icon and choose the option "Run as administrator"..Now you can create the database

answered Oct 23 '12 at 12:20

Rajesh Raj

21
1

score 1 · Answer 6 · answered Mar 16 '21 at 03:31

1

You probably selected master DB. Just switch to the database you want to write in. Go to the available databases on the top left corner in SSMS and choose the right database from the dropdown menu (see the image).

answered Mar 16 '21 at 03:31

Z-B

31
2

score 0 · Answer 7 · answered Nov 27 '12 at 17:39

If you got the same error in Sql server 2008 management studio than below link will resolve this error after so much i found this and check answer by blipsalt http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/76fc84f9-437c-4e71-ba3d-3c9ae794a7c4/

score 0 · Answer 8 · answered Jul 23 '15 at 10:50

I was also facing the same problem. After putting in a great effort I came across this beautiful link

http://www.metatexis.net/manual_server/errorwhenusingmssqlservernamedpipesprovidercouldnotopenaconnectiontosqlserver.htm

The problem with my Sql Server was that I could login using USER-NAME account but not USER-NAME\SQLEXPRESS account. USER-NAME server had just Guest permissions whereas USER-NAME\SQLEXPRESS has complete permissions. You can check that by executing this query.

select user_name()

I went on to check whether my service is running or not. You can do that by clicking start and typing in "services.msc". Open that and search for "SQL Server (SQLEXPRESS)". Start it if its not already started.

In my case it was disabled. I right clicked it. Went to properties and changed "Startup type" to "Automatic".

After doing all this I started Sql Server Management Studio again and connected using USER-NAME\SQLEXPRESS and it worked.

Cheers,

score 0 · Answer 9 · answered May 19 '21 at 10:04

0

User is not sysadmin. Login to DB with SA credentials and go to Security->Logins and select the user and right click on properties, set as follows

answered May 19 '21 at 10:04

MyNameIsDND

47
1
1
5

create table permission denied in database 'master'

9 Answers9

Linked

Related