Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am currently working with a consulting agency to set up a new production environment. They have set up a separate SQL login for each database.

However, there is no corresponding database user mapped to any of the logins, and all of the logins seem to have full access to any of the databases on the server.

In fact, each login has a check next to all the server roles.

Is this a common practice and is it safe in terms of security?

share|improve this question
add comment

2 Answers

up vote 4 down vote accepted

You can have a login to the SQL Server instance, but no access to a given database. When your login is granted access to a DB, you become a user in that DB.

As for granting max perms via roles to all logins, this is very risky, and not common, or default. Best practice is to use the very lowest perms possible that still allows users to complete their work.

share|improve this answer
    
That was my assumption. However, I don't see the login listed under the users for any of the given databases, but they still have access. Is that possible, or am I missing something? –  Jim Feb 28 '12 at 19:01
    
The roles confer access without listing the users in the roles. The users you see in a DB have explicitly granted access. –  Eric Higgins Feb 28 '12 at 19:03
    
Here's a roles list from MSFT: msdn.microsoft.com/en-us/library/ms188659.aspx –  Eric Higgins Feb 28 '12 at 19:05
    
Thanks for the link. So since the sysadmin role "can perform any activity in the server" that would include select, insert, update, delete, etc. -- is that right? –  Jim Feb 28 '12 at 19:29
    
That's exactly right. –  Eric Higgins Feb 28 '12 at 19:39
add comment

Excellent answer provided already. I just wanted to add that I've found the Microsoft SQL Server 2005 Security Best Practices to be really helpful when learning about security model of both SQL Services and users/logins.

I'm sure there's a SQL Server 2008 edition available.

Here's the link from my bookmarks.

Understanding the relationship between these concepts has often helped me when troubleshooting application errors or jobs:

  • Database User
  • database roles
  • instance-level logins
  • Windows users assigned to SQL service account
share|improve this answer
    
I fixed the link for you but it still appears to be broken...may want to check that. Going forward you can highlight a block of text, hit CTRL+L and put in the link to make it a pretty hyperlink. –  JNK Feb 28 '12 at 21:27
    
thanks for the help JNK. I struggled to find this document on MSFT servers, just google 'Microsoft SQL Server 2005 Security Best Practices', in it they describe the windows users/groups model and how that maps to each SQL Server service. The principles are valuable. –  MarcusL Mar 6 '12 at 15:59
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.