Problem
I have been tasked with auditing security on my SQL Server . However, this needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. What tables and/or views should I be using and what's the best way to extract the information out of them?
Solution
Audtiting logins is important, because it reveals who can connect to the SQL Server and what permissions they have at the server level. However, the data is at the database level and your auditing should include determining what logins can access what database. Just because a person has a login which can connect to SQL Server, that doesn't mean they have the ability to enter a database. They must be specifically granted permission to do so, with two exceptions, both of which are system databases :
- master
- tempdb
Everyone has access to these two databases because the guest user is enabled on both of them. This is a required configuration because there is functionality in both databases that potentially all logins will require access to. Therefore, if an auditor inquires about why the guest user is enabled in either of these databases, it is by design.
So that leaves all the other databases to query in order to determine which logins have access and how they map into those databases. This information is important because you'll eventually use it to determine what permissions a given person has in the database. But first steps first. Let's find a list of databases.
In SQL Server 2000, the following query will pull back all databases, with the exception of master and tempdb. I've intentionally included model, because all new databases will be created from a copy of model. Therefore, it's important to check on model as well.
For SQL Server 2005 and above, we use the sys.databases catalog view:
In order to determine what databases a login has access to, each database must be queried individually. There's nothing at the server level which provides all of the information we need. However, we do need to determine what the SID, or security identifier, is for each login, because that's how SQL Server maps a login to a database user.
In SQL Server 2000, we can query syslogins to get the name and the SID:
In SQL Server 2005 and above, we'll use the sys.server_principals catalog view:
Now, when we're talking about matching up the SID, we'll either do so by joining to the sysusers table (SQL Server 2000) or sys.database_principals catalog view (SQL Server 2005+). This can actually be done from the master database, using a three part naming convention ([database].[owner].[object] or [database].[schema].[object]) like so.
Here I've got the AdventureWorks2008 database installed and I'm trying to match up logins at the server level to databases users within the AdventureWorks2008 database:
or for 2005+:
If we need to audit all access at once, we can build a dynamic SQL string and then execute it. This will give us back a single result with all the databases and all the mappings from login to user.
Here's how to do it in SQL Server 2000:
and again in SQL Server 2005 and above:
No comments :
Post a Comment