create table permission denied in database 'master'

R

9

14

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.

Rauscher answered 5/9, 2011 at 22:2 Comment(2)

Have you removed yourself from the sysadmin role or something? Also what OS are you on? – Schorl 5/9, 2011 at 22:17

Just read something that suggests running Sql Studio as administrator may help - on vista anyway - not sure if that helps – Lavonnelaw 5/9, 2011 at 22:42

L

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

Lavonnelaw answered 5/9, 2011 at 22:15 Comment(5)

Sounds like the login the OP is using won't have CREATE DATABASE permissions. – Schorl 5/9, 2011 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 – Rauscher 5/9, 2011 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 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. – Illinois 22/6, 2012 at 19:55

link has changed and can currently be found at gist.github.com/hugodahl/8b34cbacf7bcd491b116 (according to hrzdata.com/node/57) – Damico 27/10, 2016 at 15:17

when installing sql server, did you add your current user as an admin to the server? – Kerriekerrigan 27/10, 2016 at 15:43

H

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/

Hombre answered 8/11, 2012 at 15:49 Comment(0)

A

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.

Artistry answered 9/12, 2018 at 23:5 Comment(0)

C

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.

Competent answered 28/2, 2014 at 14:36 Comment(2)

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: imgur.com/a/mSFJe – Necessitous 30/8, 2017 at 21:31

Thanks Jeff, that was very helpful. I'm going to post the visual in the answer to make it stand out a bit more. Cheers. – Artistry 9/12, 2018 at 22:40

C

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

Cagey answered 23/10, 2012 at 12:20 Comment(0)

E

2

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

Europeanize answered 19/5, 2021 at 10:4 Comment(0)

U

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).

Unthankful answered 16/3, 2021 at 3:31 Comment(0)

C

0

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/

Chesterton answered 27/11, 2012 at 17:39 Comment(0)

D

0

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,

Domett answered 23/7, 2015 at 10:50 Comment(0)

Recommended topics

Hot tags