Grant Execute Permissions for all Stored Procedures 2005

Like the new Database Scope feature in 2005 as concerned with Granting execute permissions. You can now create a role and then grant execute at the Database level to give the role execute permissions on all stored procedures:

CREATE ROLE db_executor


Backup Database to UNC

Mainly to remind me how to backup from SQL Server to a UNC, but maybe this will provide you with a quick step by step and save you some time. Details, performing a Full Database Backup of a SQL Server 2000 database to a UNC. Both the source and destination servers are in the same domain. The SQL Server Service and the SQL Server Agent Service are both started using a domain account.

  1. Set the SQL Server Server service to start as a domain account that has appropriate permissions (Read/Write) to your UNC path
  2. Run the setspn tool to register your domain user servicePrincipalName field in AD. You can use ADSIEDIT.msc console to check the status of the command. setspn MSSQLSvc/ Domain\User [see my Epic quest article for more details]
  3. Create the SQL backup job to backup the database to the UNC path: BACKUP DATABASE [dbname] TO  DISK = N’\\servername\share_name\filename.bak’ WITH  INIT ,  NOUNLOAD ,  NAME = N’backup name’,  NOSKIP ,  STATS = 10,  NOFORMAT
  4. Celebrate a job well done, perhaps with a Mt. Dew or your choice of work approved beverage.

Server Role vs Database Role

I needed a little refresher the other day on Server roles vs. Database roles in SQL Server 2000. So I created a SQL User called test and gave him no access. I then opened up a query analyzer window and logged in as my new user. I was able to login successfully. I could see master, msdb, and tempdb in the database dropdown box. I tried a quick query on msdb:

select * from sysjobs

Result: Server: Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object ‘sysjobs’, database ‘msdb’, owner ‘dbo’.

However using the stored procedure  sp_helprotect, I started to determine what access my test user had to each database. From BOL: SP_HELPROTECT: Returns a report with information about user permissions for an object, or statement permissions, in the current database. After running this against a user against on the master and msdb you quickly realize that the default created user has a lot of access.

The default user can glean the following information (and a lot more)

  • Database file location, name, etc from sysfiles in msdb
  • User information from sysusers
  • User information from syslogins. Syslogins is interesting because if you do a SELECT * from it you get the permissions error. Inspection of the output from the sp_helprotect shows which columns in syslogins that you have access to. So just alter your select to select the fields you have access to.
  • Information about objects from the sysobjects table
  • Etc. Etc.

It feels like I am just scratching the surface. In the weeks to come I am going to set aside some time to come up with a security audit technique. Anyway, to continue on with my testing I then gave the user some fixed server roles. Tested some queries, rinse repeat, etc.

Lessons Learned (or re-learned):

  • Do not give sysadmin fixed server role out unless it is absolutely justified.
  • Default users with no access have quite a bit of access to the information about your server/database
  • Used the database roles to give access to specific databases, used the fixed server roles to give access server wide.
  • Be as granular as possible when diving out permissions
  • Use the sp_helprotect stored procedure to audit what permissions a user has. Make sure application users do not have to many permissions.

Epic quest: Cannot generate SSPI context

I think I made this way too difficult. The problem first started when one of our Windows 2003 servers crapped out. Total hardware failure. The fix was to replace the server and re-configure. After the new server was in place and working fine, the SQL backup job started failing. The SQL Server was a separate server using a share on the file server via UNC. For the article I will call the File Server FILESERVER1 and the SQL Server SQLSERVER1.

I noticed that the SQL Server Service was using LocalSystem to start the service. The odd thing is that the SQL Server backup job was working fine before the FILESERVER1 failed using the LocalSystem account. I thought this was odd because I have read several articles stating that if you want to backup to a UNC the SQL Server Service must be a domain user and have the appopriate permissions to the share. So I thought, no sweat. I will just change the SQL Server Service to use a domain user with privledges and away we go. I changed the service and got approved for a quick Service Restart. The Service started fine and I could browse the server fine locally using Enterprise Manager.

I then try to hit the server from my laptop using Windows Authentication from Enterprise Manager and received the following error: Cannot generate SSPI context error. I quickly just changed the service back to LocalSystem so I could do some testing on a test box, trying to reproduce the error.

Found this article from Microsoft: This is a very good description of the problem and some possible solutions. The article links off to this article which contained the solution to the problem:

Using the setspn resource tool was an interesting adventure. First off I am not a Domain Admin so I had to have some assistance from our Server team. Had a Domain Admin run the following command

setspn -A MSSQLSvc/ sqlaccount

You will need to use the Fully Qualified Name here. You will need to run this for every server you would like to start using a Domain User account. The sqlaccount is the actual Domain User account you want to use. Refer to the Security Account Delegation Article for more details such as what to do if the domains do not match, etc. One gotcha is that it took a little while for the setspn command to make the change to the domain user. I assume it was some kind of Synchronization delay.

While browsing around for the error I sumbled on the ADSI Edit MSC. This is helpful to look at to review the setspn change. You can find the user in the domain and view the property. If you are a Domain Admin you can actually make the change from the ADSIEdit console. see below for screenshots from ADSIEdit:

So after the user was given the attribute for SQLSERVER1, I then hoped back on the server and restarted the SQL Service. Service Started fine, I was able to browse to it from Enterprise Manager on my laptop, and the UNC backup to FILESERVER1 started working again.

Here is another good Microsoft KB that shows how to setup Windows Services Accounts for SQL Server:

Deny Domain Admin access to SQL

First thoughts said this was not really possible. Seems like Domain Admins (DA) could access the SQL Server regardless of what I did to lock them out. I did find one way that will satisfy an auditor.

  1. Create a Domain group and add the Domain Admins you wish to lockout.
  2. Add the Domain group as a user to your SQL Server
  3. On the General tab under Security Access click Deny access.
  4. Make sure your

Yeah I agree that seems too simple. One major flaw with this is that a DA could just remove himself/herself from the group you specified. Another flaw is that the DA could just access the server itself and stop services, delete databases, reign down terror etc.

I searched and found some sites that suggested removing the BUILTIN\Administrators group from SQL. NOTE: if you do this make sure you know what you are doing. If you do this in the wrong order you can remove all Sysadmin access from your instance of SQL. Make sure you have an account that has sysadmin privs before you drop the BUILTIN\Administrators group from your SQL.

During testing I did not see a need to remove the BUILTIN\Administrators group.


  1. Add Domain Admins to Domain Group
  2. Deny the Group access to SQL using steps listed above
  3. Have Domain Admin try connecting to Enterprise Manager/SQL Analyzer/ODBC from their workstation using Windows Auth. Make sure the login fails
  4. Have Domain Admin try connecting to the server and using Enterprise Manager/SQL Analyzer/ODBC from the server itself.

If you have a better plan then this, then by all means please share it. I am probably missing something here.