User does not have permission to perform this action( Microsoft SQL Server, Error:15247)

Question

 

Hi,

I was able to do my work with ease on my Database server, until sometime Friday evening. Don’t exactly know what transpired, only that I can now no longer run queries or even create new databases. Here is a sample of what happens when I try to create a database

Create Database PetitionSample

Msg 262, Level 14, State 1, Line 1

CREATE DATABASE permission denied in database 'master'.

I used to be able to create databases, create new users and so on. My Server is installed on a Vista machine and I am running Developer version and using Management studio 2008 to access it. Anyone have any idea what could be going on?

---

None

Answer 1

I think this is something to do with windows Vista.
See the link
How to: Connect to SQL Server from Windows Vista

---

Mangal Pardeshi. You can turn off the SUN, but I'm still gonna shine. ;)

Answer 2

I tried the examples of the page and got the same error each time


An Error Occured while executing a Transact-SQL Statement or batch
User does not have permission to perform this action( Microsoft SQL Server, Error:15247)

---

None

Answer 3

So How do you suppose I go about this? Uninstall the instance of SQL Server completely and then reinstall ? I am able to open and access the database when I run as administrator but just not able to do much else after wards.

---

None

Answer 4

No, You don't need to install again, you just need to give rights to your login by logging in with sysadmin user.  If server is running on mixed mode, log in with sa user and give your login required permissions. 

First try to log in with sa, if sa is not enable, tell us, I will guide you accordingly.

---

Mangal Pardeshi. You can turn off the SUN, but I'm still gonna shine. ;)

Answer 5

I have the same issue as in this post. The sa account is disabled.  What can I do to solve this issue?

Answer 6

i got the same problem and i solve it
firstly you must use windows authentication mode and go to security then logins
and tick the box es that is it if you have any question contact me
[e-mail]

Answer 7

Hi

Did you guys solve this thing in the end?

I'm currently trying to do the same - having a similar issue getting the correct permission in SSME 2008 - right clicking so I can work as system admin - still no luck - final explaination confused me - being a newbie.

Answer 8

If your sa account is disabled , login with windows authentication and make sure you have your authentication mode set to MIXED mode and now you can enable your sa login using

 

ALTER LOGIN [sa] ENABLE

---

Thanks, Leks

Answer 9

Hi Leks,

Thanks for responding.  Appreciated.

Alas, in following your instructions I have a problem when I attempt to change authentication mode to mixed.

The message being:


TITLE: Microsoft SQL Server Management Studio
------------------------------

Alter failed for Server 'PMC-PC\sqlexpress'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.0.2531.0+((Katmai_PCU_Main).090329-1015+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

xp_regread() returned error 5, 'Access is denied.'
xp_regread() returned error 5, 'Access is denied.'
xp_regread() returned error 5, 'Access is denied.' (Microsoft SQL Server, Error: 22001)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.2531&EvtSrc=MSSQLServer&EvtID=22001&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------


Needless to say, at this stage I have no idea what the resolution to all this is, so any more thoughts would be extremely appreciated!

Answer 10

When you change some server level settings with respect to SQL server , that has to be updated in the windows registry.

This kind of server level changes like authentication modes are to be done by a login that has sysadmin privilege in the SQL server . By default sysadmin logins has got privileges to execute xp_regwrite

Stored procs . If you are using windows vista , try starting the SSMS with run as administrator option.

 

Bottom line : this is a permission issue so perform this action with sysadmin account and you will succeed , remember to restart SQL services once the authentication mode is changed.

 

 

---

Thanks, Leks

Answer 11

Hi Leks,

I'm having the same problem on Windows 7.  Sql doesn't recognize Windows 7 Administrative privileges so it denies permission to grant any changes to Sql Server.  Can't add users, can't change permissions for users.  Can't use sa ID because don't know the password set up by the system.  It's not my Windows admin password... It's a viscious circle - want to add user with admin level permission but have to have admin level permission to do so...

Thanks for any help.

Charles

Answer 12

I am stuck in this same scenario.  I am a local administrator on the box, in the administrators group.  I cannot login as sa because the account is disabled and because mixed-mode authentication is off.  It will not let me enable the user, or enable mixed mode authentication (the xp_regread() error).  I cannot add "sysadmin" rights to any logins because "User does not have permission to perform this action."  I even tried changing the service to run as me instead of Network Service.  I tried running as the user named "Administrator"

It is a bit strange that I can modify any single bit on the entire hard drive, delete, overwrite, and modify files.  Yet I can't change the permissions on something.  This has to be a bug of some kind.  FYI: This is SQL Server 2008 Express x64 w/ SP2 on Windows 7 x64.

Answer 13

I found a solution.  Basically, run SQL server in Single-user maintenance mode, and you will get access to the server again.  You must be an administrative user for this to work.

NOTE: In all of the examples below, you may have to change parameters or command-lines based on your server name and instance name.

I. Force SQL server to support mixed-mode authentication.
1. Run REGEDIT
2. Go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer
NOTE: This key may vary slightly based on the installed version and instance name.
3. Set "LoginMode" to 2.
4. Restart SQL Server.
(Source: http://support.microsoft.com/kb/285097 )

II. Force SQL server to let you in temporarily
1. Go to services.
2. Stop SQL Server.
3. Grab the SQL server command-line (right click the service - properties).  Mine is:
"C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -sSQLEXPRESS
4. Open an administrative command prompt.
5. Run the command-line from step 3, but add -m -c for single-user maintenance mode command-line.
6. Open another administrative command prompt.
7. Run "sqlcmd -S localhost\SQLEXPRESS" from that same directory (replace with your server and instance name)
8. Now you can do all the stuff everyone told you to do that didn't work.  For example, to create a hero user with administrative accss:

CREATE LOGIN hero WITH PASSWORD='123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXEC sys.sp_addsrvrolemember @loginname = 'hero', @rolename = 'sysadmin'
GO

9. QUIT and close the command-prompt
10. Go to the SQL Server command-line window and hit ctrl+C.  It will prompt "Do you wish to shutdown SQL Server (Y/N)?" and enter Y.
11. Close the command-prompt
(Source: http://msdn.microsoft.com/en-us/library/dd207004.aspx )

III. Finally, login using your hero:
1. Restart the SQL Server service
2. Login using SQL Server authentication as the user "hero" with password "123"
3. *BAM* now you are in.  Now give yourself sysadmin access and delete the temporary user.

Answer 14

The solution laid out by Moby Disk worked for me with one modification.

 

In step II, number 7 two methods could be used:

Number 1: after running the command in step 5, scroll up through the output and find the lines that begin with "Server local connection provider is ready to accept connection on [USE THIS NAME WITH THE -S SWITCH]"  There are two of them when I run the command.  The first ("\\.\pipe\SQLLocal\MSSQLSERVER" ...obviously replace this with your output) worked for me, but if not, try the second ("\\.\pipe\sql\query" ...again... obviously replace this with your output).

Number 2: When using the "-S" switch and then my database instance name, I kept receiving the error: "hresult 0x2 level 16 state 1 named pipes provider could not open..."

Instead of the -S switch, use the -H switch followed by the SPN given when the ctrl+C command (step 10) was executed.  Obviously you will have to start again from step 3 in Moby Disk's post at this point since you have shut down your database instance.

 

Also, and less importantly, the parameter in step II, number 8 is "@loginame", not "@loginname".

 

Thank you so much for the help!

Answer 15

A Great Help.  Thank you.  Worked without issue.

Answer 16

That really helped.  Thank you very much.

Answer 17

Hi ,

My Sa account is disabled.

saritha.

Answer 18

Hi,

I followed the instructions. However I couldn't see the new login 'hero' in the logins list in SSMS using windows authentication(administrator) . Instead of creating new login, I tried below commands and am able to login with sa successfully.

ALTER LOGIN sa enable
GO
ALTER LOGIN sa WITH PASSWORD = ‘abc123’, CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF
GO

Thanks.

Sundar