Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm in the administrators group on a Windows 2008 database server.

My username appears in the Security -> Logins section of the SQL Server Management Studio and under Properties -> Server Roles I see I have the "public" role only. Under "User Mapping" I'm a member of the "db_datareader" and "public" roles.

I need to add myself to the "db_datawriter" role. When I try, I get error 15247 - User does not have permission to perform this action.

Is there anything I can do to give myself this role? As a Windows administrator on the box I would have thought that it would be possible...

Thanks in advance for helping a very out-of-his-depth developer :)

share|improve this question
add comment

3 Answers

up vote 3 down vote accepted

In SQL Server 2008, Windows admins are no longer SQL Server admins by default. You will have to ask somebody else to change your permissions.

http://msdn.microsoft.com/en-us/library/cc280562(v=SQL.100).aspx

share|improve this answer
    
Thank you for your answer. I would vote you up, but I don't have enough rep... –  Cosmic Flame Dec 3 '10 at 20:22
add comment

I tried using the above approach for SQLExpress to no avail. For whatever reason, adding the -m flag to the Startup Parameters was causing SQLExpress to fail on start up. So I did some searching and found this blog post which seemed to do the trick of getting SQLExpress into Single-user Mode, and then followed yrushka's steps for adding a user.

  1. Stop SQL Express

    NET STOP MSSQL$SQLEXPRESS
    
  2. Start SQL Express

    NET START MSSQL$SQLEXPRESS /m
    
  3. Open a command prompt window and log into SQLExpress

    sqlcmd -S SERVERNAME -E 
    
  4. Add a user

    EXEC sp_addsrvrolemember 'DOMAIN\YOURNAME', 'sysadmin';
    GO
    
share|improve this answer
add comment

You can add yourself even if you are not sysadmin on SQL Server but are part of administrators group.

  1. Start SQL in single mode: In SQL configuration Manager --> select SQL Server service --> --> go to Advanced --> go to startup parameters --> add “;–m” to the end of the list in the “Startup parameters” option.
  2. start command prompt and connect to SQL through sqlcmd using windows authentication.

sqlcmd -S SERVERNAME -E

  1. TYPE IN:

EXEC sp_addsrvrolemember 'DOMAIN\YOURNAME', 'sysadmin';

GO

share|improve this answer
    
Cunning. Presumably this involves completely stopping the SQL server/service first? I.E. The database on the server wouldn't be accesibly while making the change? –  Cosmic Flame Dec 6 '10 at 19:41
    
yes, it assumes stopping and starting SQL again. Of course in production environment is not advised to do so, as there must be a DBA assigned for this. –  yrushka Jan 4 '11 at 10:50
    
Awesome, that worked for me. Thanks very much! –  EMP Mar 7 '11 at 6:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.