none
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
    Thursday, January 15, 2009 4:38 AM

Answers

All replies

  • 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. ;)
    Thursday, January 15, 2009 7:21 AM
    Moderator
  • 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
    Thursday, January 15, 2009 12:51 PM
  • 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
    Thursday, January 15, 2009 6:48 PM
  • 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. ;)
    Friday, January 16, 2009 5:02 AM
    Moderator
  • I have the same issue as in this post. The sa account is disabled.  What can I do to solve this issue?
    Tuesday, March 24, 2009 5:18 PM
  • 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
    yavuzca23@hotmail.com
    Tuesday, July 07, 2009 1:49 PM
  • 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.
    Monday, January 25, 2010 8:16 PM
  • 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
    Monday, January 25, 2010 10:39 PM
  • 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!

    Wednesday, January 27, 2010 1:40 PM
  • 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
    Thursday, January 28, 2010 2:57 AM
  • 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

    • Proposed as answer by plang321 Thursday, March 24, 2011 6:23 PM
    Monday, September 27, 2010 8:11 PM
  • 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.

    Wednesday, December 15, 2010 6:45 PM
  • 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.

    • Proposed as answer by Moby Disk Wednesday, December 15, 2010 7:39 PM
    Wednesday, December 15, 2010 7:39 PM
  • 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!




    • Proposed as answer by clorkster Thursday, September 01, 2011 1:53 PM
    Thursday, September 01, 2011 1:41 PM
  • A Great Help.  Thank you.  Worked without issue.
    Wednesday, November 16, 2011 5:13 PM
  • That really helped.  Thank you very much.
    • Proposed as answer by ShibinRaj Friday, March 16, 2012 8:12 AM
    Tuesday, March 06, 2012 10:26 PM
  • Hi ,

    My Sa account is disabled.

    saritha.

    Wednesday, July 25, 2012 4:15 PM
  • 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

    Monday, March 25, 2013 12:16 PM
  • I just signed up to say THANK YOU
    Thursday, December 05, 2013 11:59 PM
  • This is great, BUT what if i don't have admin rights to begin with?
    Wednesday, February 05, 2014 12:47 AM
  • This is great, BUT what if i don't have admin rights to begin with?

    You need admin rights on operating system or SQL.

    http://sqlserver-help.com/2012/02/08/help-i-lost-sa-password-and-no-one-has-system-administrator-sysadmin-permission-what-should-i-do/


    Balmukund Lakhani
    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter | Facebook
    Author: SQL Server 2012 AlwaysOn - Paperback, Kindle

    Wednesday, February 05, 2014 12:57 AM
    Moderator
  • Hello - and thanks first for the thread.

    So I have this same problem - 15247 - trying to install and set up SQL Express 2012 - Windows 7.  I get this error when I log in using Windows authentication and try to create a new user.  I get error 223 when I try to log in as sa (No process is on the other end of the pipe).

    I am an admin - and to make absolutely sure, I uninstalled SQL Server, have changed my pc from a work network PC to a standalone, reinstalled SQL and tried again.  Exactly the same problem.

    I tried to follow the steps above, but when I run the command "C:\Program Files -17058, Severity 16, state 1.  And then 'could not open error log file'.  "Operating system error = 5(error not found)"

    So I seem to be completely stuck.

    Any thoughts?

    Cheers

    Monday, March 03, 2014 5:04 AM
  • Rather than piggybacking to a thread from 2009, why not start from the beginning. It's not even clear to me if you get an error when you attempt install SQL 2012, or if it happens later. Just because you got the same error as in this thread, does not mean that the reason is the same.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, March 03, 2014 10:50 PM
  • OK.  I'll start a new thread.  I thought the preferred etiquette was that folks didn't do that... but fine.
    Tuesday, March 04, 2014 2:58 AM