Problem
I have been tasked with auditing security on my SQL Server. I understand that logins allow you to connect to SQL Server, but I'm not quite understanding how to determine whether a login has access to a database or not. For instance, I know that all logins can access the master database, but when I look at a login in SQL Server Management Studio, I don't see a checkbox beside the master db for that login. How can I determine what databases a login has access to?
Solution
You're right, that if you look in SQL Server Management Studio you won't see a login's access to the master database unless the DBA has done something explicitly. An example is shown in Figure 1.
Figure 1:
But we do know that all logins can access the master database. So let's talk about how a login can connect to a given database. There are five possible ways a login can have permission to connect to a given database:
- Explicit access is granted.
- The login is a member of the sysadmin fixed server role.
- The login has CONTROL SERVER permissions (SQL Server 2005/2008 only).
- The login is the owner of the database.
- The guest user is enabled on the database.
Explicit Access (Login Mapped to Database User):
The first way is if a login is given explicit access to a database. For instance, in SQL Server 2000, if I had a user MyTestUser, I would grant access like so from within the database:
In SQL Server 2005 and 2008 there are new T-SQL commands to create logins and users. So I would use the following command to do the same thing:
A login granted access in this manner should appear in the sysusers table (SQL Server 2000) or the sys.database_principals catalog view (SQL Server 2005/2008). For instance, here's how I would match up users in a given database to their corresponding logins (SQL Server 2000):
And here's how we'd do it in SQL Server 2005/2008:
If you see a login match up to a user in this manner, then the login has access to the database.
Implicit Access (Member of Sysadmin Fixed Server Role):
All members of the sysadmin fixed server role map to the dbo user of every database. Therefore, if a login is a member of this role, it automatically has access to every database.
Here is the query for SQL 2000 to see members of the sysadmin fixed server role.
Here is the query for SQL 2005/2008 to see members of the sysadmin fixed server role.
Implicit Access (CONTROL SERVER permission - SQL Server 2005/2008):
The CONTROL SERVER permission gives equivalent rights as a member of the sysadmin role with a few exceptions, which aren't of importance here. Therefore, if a login doesn't map explicitly to a user in a database, but that login has CONTROL SERVER permissions, that login can still access the database. You can see who has CONTROL SERVER permissions by the following query:
Implicit Access (Database Owner):
The database owner automatically maps into the database as the dbo user. The query given under explicit access should reveal the owner by just looking at the dbo user. However, another way is to query the sysdatabases table (SQL Server 2000) or sys.databases catalog view (SQL Server 2005/2008). Here's the SQL Server 2000 query that reveals all the owners of all the databases on the server:
And here's how to do the same thing in SQL Server 2005/2008:
Implicit Access (Guest User Is Enabled):
The final way a login can get access to a database is if the guest user is enabled for that database. If a login cannot map in any other way, it'll use guest if that's available. That's actually how logins can access the master database. The guest user is enabled. With respect to user databases, the guest user should only be enabled in special cases. The default is for it to be disabled. However, there are two system databases which the guest user must always remain enabled. They are:
- master
- tempdb
And that explains why logins always have access to master, even when explicit rights aren't visible. To see if the guest user is enabled we can query sysusers (SQL Server 2000) or sys.database_permissions (SQL Server 2005/2008). Here's how to do it in SQL Server 2000:
In SQL Server 2005/2008 we have to look for the existence of the CONNECT permission at the database level for the guest user. If it exists, the guest user is enabled. If it doesn't, then the guest user is not.
No comments :
Post a Comment