3

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?

Improve this question

2 Answers 2

Reset to default
3

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.

Improve this answer
5
  • 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?
    – user7065
    Feb 28, 2012 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, 2012 at 19:03
  • Here's a roles list from MSFT: msdn.microsoft.com/en-us/library/ms188659.aspx
    – Eric Higgins
    Feb 28, 2012 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?
    – user7065
    Feb 28, 2012 at 19:29
  • That's exactly right.
    – Eric Higgins
    Feb 28, 2012 at 19:39
2

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
Improve this answer
2
  • 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, 2012 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.
    – MEL
    Mar 6, 2012 at 15:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.