Update permission denied, can't give self db_datawriter permission despite being admin on box

Question

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

Answer 1

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

Answer 2

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

Answer 3

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