FAQ

FAQ: How to troubleshoot and grant SQL Server permissions

  Troubleshooting permissions problems in SQL Server can be an arduous process that slows down your users and limits their productivity. In this collection of frequently asked questions, find out how to restore lost permissions, limit permissions for specific

    Requires Free Membership to View

users, grant permissions to a group rather than a single user and other assorted SQL Server permission topics.


TROUBLESHOOTING SQL SERVER PERMISSIONS PROBLEMS

  1. How can I limit SQL Server admin permissions for domain accounts?
  2. Should developers have permissions to SQL Server production queue?
  3. Do I have a permissions or an authentication problem in SQL 2005?
  4. How can I display all the permissions a SQL user or login name has?
  5. How can I grant an id permission to create and modify database tables?
  6. How can I fix a SQL Server permissions error when connecting to .NET?
  7. Is it possible to return my SA permissions to my SQL Server?
  8. How can I grant SQL 2005 login and database permissions to a group?
  9. Can I find an explanation for the permissions of databases?
  10. What are the best practices for SQL Server 2005 developer permissions?

  1.  How can I limit SQL Server admin permissions for domain accounts?

The SQL Server should never be run under an account with domain admin permissions. Always grant the Windows account that the SQL Server runs under the minimal rights it needs in order to function. The lowest set of permissions that Microsoft SQL Server needs to run are the "Log on as a service," "Log on as a batch job," and, if running Enterprise Edition, the "Lock pages in memory" rights.

Many people will give the SQL Server local administrative rights to the server. The SQL Server account should never be given domain admin rights, as this poses an unacceptable security risk to the Windows domain.
—Denny Cherry, Performance Tuning Expert

 Return to SQL Server permissions FAQs

  2. Should developers have permissions to the SQL Server production queue?

Some of our developers feel they should have access to the production jobs queue. If we grant this permission, it's all or none. Is there a reasonable, objective solution?

SQL Server 2005 did introduce more granular permissions, but not quite at the level you may have wished for in this case. If their needs are simple, have you considered giving them access to a proc, executed WITH EXECUTEAS that can do the job management for them?
—Greg Low, Development Expert

Return to SQL Server permissions FAQs

  3. Do I have a permissions or an authentication problem in SQL Server 2005?

For the past several weeks, I've been able to access my SQL Server 2005 data from within my .NET project, but not from outside it. I get the same "Login Failed for user NT_AUTHORITY/NETWORK SERVICE" error, no matter where I attempt to access it from. I think it's a permissions error, because one test project receives the error when attempting to save state via PROFILES and the other receives it when the code attempts to gather data into a dataset. What can I do about this error?

I would try to confirm it is in fact a permisisons issue by going to the IIS settings for the virtual site and deactivating Anonymous access. Enable either Integrated Windows access or Basic auth. Next, go to the website as usual. You will be prompted to login with an HTTP/1.0 old-style login popup. Use any account that you know has read/write permisisons to the directory where the site files are stored.

If everything works fine, then it is definitely an authentication issue. Now, go back to anonymous access and start adding permissions to the IUSR_SERVER account to see if you can get the application to write properly. I would also suggest (if possible) downloading the excellent FileMon tool from SysInternals and see which read/write accesses are being denied. That will help you hunt down the issue.
—Steven Andres, Security Expert

Return to SQL Server permissions FAQs

  4. How can I display all the permissions a user or login name has?

SQL Server includes a procedure which I'm not fond of. It's called sp_helprotect. However I've got some code that I've thrown together which will display this data nicely and in a single call, instead of the mess of code within the procedure.

 select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
sys.database_permissions.permission_name,
sys.database_permissions.state permission_state,
sys.database_permissions.state_desc,
state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id =
sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id =
sys.database_principals.principal_id
order by 1, 2, 3, 5

—Denny Cherry, Performance Tuning Expert

Return to SQL Server permissions FAQs

  5.  How can I grant an id permission to create and modify database tables?

If you're on SQL Server 2005, consider creating a schema and giving the user permissions on the schema instead of the whole database.
—Greg Low, Development Expert

Return to SQL Server permissions FAQs

  6. How can I fix a SQL Server permissions error when connecting to .NET?

When I try to connect a project developed in .NET with SQL Server as back end in the client system, the following error is displayed:
"Request for the permission of type system.security.permissions.fileopermission, mscorlib, version = 1.0.3300.0, culture = neutral, public key = b77a5c561934e089 failed". What might this mean?

I'm presuming this error actually says system.security.permissions.fileiopermission. In that case, this won't be due to accessing SQL Server, at least the direct cause of the error. This error relates to your program not being able to perform I/O operations on a file on the disk. Check your CAS (code access security) settings for the program.
—Greg Low, Development Expert

Return to SQL Server permissions FAQs

  7. Is it possible to return my SA permissions to my SQL Server?

I have somehow removed my System Administrator permissions from my SQL Server, and I don't know the SQL SA password. Is there any other way to restore my SA access to SQL?

By default, all members of the Administrators group will be members of the sysadmin fixed server role.

If, however, you have removed the BUILTINAdministrators group from the sysadmin fixed server role, you have a couple of things to check.

 

  1. If the server is setup for mixed mode authentication, log into the SQL Server using the sa account. The password will be the password you set when you installed the SQL Server.
  2. If another login is setup as a member of the sysadmin fixed server role, connect with this account and restore your login rights to the database.
  3. If you are using SQL Server 2000, you can rebuild the master database by using the rebuildm.exe utility which comes with SQL Server 2000. After using the rebuild.exe, you will need to recreate all the logins for you users and applications, as well as attach any user databases which you have created on the server.
  4. If you have a backup of the master database from before you lost the permissions, you can restore this master database to another SQL Server. Then you would have to stop both the SQL Server you restored the master database to, as well as the database you need to fix, and copy the physical files which make up the master database to the server which the incorrect rights. When you restart the SQL Server, some of your user databases may show suspect. You can use the ALTER DATABASE command to bring them back online, or detach them and reattach them.

—Denny Cherry, Performance Tuning Expert

Return to SQL Server permissions FAQs

  8.  How can I grant SQL 2005 login and database permissions to a group?

First you need to create a login to the SQL Server for that domain group. In Management Studio connect to the SQL Server in the object explorer. Navigate to the ServerName > Security > Logins. Right click on Logins and select New Login. Select the Windows authentication radio button and click the search button. Type in the name of your domain group and click Check Names. It should find it, then click OK (you may need to click advanced to get it to search easier. After you are back on the New Login screen select the User Mapping page on the upper left.

Put a check mark next to the database you want to give them access to, and in the box below select the role or roles you wish to give the users access to.
—Denny Cherry, Performance Tuning Expert

Return to SQL Server permissions FAQs

  9. Can I find an explanation for the permissions of databases?

Is there anywhere that I can find out an explanation of what the database permissions are? For example, what does GRANT VIEW DEFINITION do?

Books online can provide you with the bulk of this information, but most GRANT statements do what the name says.

For example GRANT VIEW DEFINITION allows the user to view the Definition (source code) of any view, procedure, function that you have granted the right to. If you run:

GRANT VIEW DEFINITION ON schema::dbo TO Bob

Bob will be able to view the definition of all objects within the schema dbo. If you run:

GRANT VIEW DEFINITION ON vw_MyView TO Bob

Then Bob can view the definition of the view vw_MyView. Within SQL Server, the definition would be the table structure (the columns, indexes, etc) if it's a table. If it's a view, procedure, function, rule, etc then it will show you the code which creates that object.
—Denny Cherry, Performance Tuning Expert

Return to SQL Server permissions FAQs

  10. What are the best practices for SQL Server 2005 developer permissions?

We're having trouble scheduling and editing SSIS jobs in SQL Server 2005. The SA has created a proxy account for us to create SSIS packages. We're able to create jobs and schedules, but they're not running. Also, once we create a job and a schedule, we're not able to edit them – all the options are grayed out except "View". We believe it has to do with permissions, since we're all supposed to have the correct level of access. What are we doing wrong?

SQL Server 2005 introduces the following MSDB database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

Before SQL 2005, I've always had problems with non-sa users being able to see jobs they don't own. We couldn't even tell if someone else's job was running or not. Your DBA should be able to create a proxy account with one or more of these new SQLAgent roles enable. They are explained in detail in MSDN at this link, which provides the exact permissions associated with these new roles.
—Joe Toscano, Development Expert

Return to SQL Server permissions FAQs

 

Didn't find what you were looking for?   
 

Join the thousands of SQL Server pros who are finding the answers they need on our IT Knowledge Exchange. Discover an IT community where real-world knowledge and experience is shared among industry peers.

Another option is to visit our Ask the Expert section where you can browse Q&As or submit a question directly to one of our SQL Server experts.

 

This was first published in September 2008

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: