Menu Bar

Wednesday, September 21, 2011

Scripting users and their permissions (part 2)

"A man walks down the street in that hat, people know he's not afraid of anything." - Wash, to Jayne - Firefly, "The Message"In my last post I talked about scripting out users and their permissions in Oracle.

Since his is a blog about things both Oracle and SQL Server, it only stands to reason that the following post should be about replicating this for SQL Server.

For those more familiar with Oracle than SQL Server, there's two layers of objects between you and your data. Since a SQL Server instance has multiple databases in the one instance (which is completely different to the way a lot of Oracle databases are set up), you first need a login name to log in to the instance. This can be a local SQL Server login, or a Windows Account.

Once authenticated to the SQL Server instance, there are then users in each database that are linked back to logins. So if you have Prod, Test and Dev databases on the one server, you could login as "myuser" and possibly have full database ownership on Dev, Read and Update to Test and Read Only to Prod. The one login gets you into the instance, but the users connected to the login in each database have varying permissions.

So, with that out of the way, the first thing we need to get is a list of logins. Login information is stored in the master database of the instance.

The easiest way to get this information is to go straight to the source. Microsoft provides a script to extract this information from you database using this Knowledge Base article. The script is too long to reproduce here, but run it to create two stored procedures in master and then "exec sp_help_revlogin" to generate a list of logins on your instance.

Once we have the list of logins, the next thing we're going to need is a list of users in the database. You run this out of the database you're extracting the information from

use ellprd
go
select 'create user ' + QUOTENAME(a.name) +
CASE WHEN SUSER_SNAME(a.sid) is NULL
then ''
ELSE ' for login ' + QUOTENAME(SUSER_SNAME(a.sid))
END +
CASE WHEN default_schema_name is NULL
then ''
ELSE ' with default_schema = ' + QUOTENAME(a.default_schema_name)
END
from sys.database_principals a, sys.sysusers b
where (type_desc like '%USER' or type_desc = 'WINDOWS_GROUP')
and a.name = b.name
and b.altuid is null
and b.sid is not null

Finally, we need to get a list of all the object permissions in the database.

For this, the magic of Google comes to my aid yet again. Found this post which has a lovely script that generates user permissions. There are two variables at the top - @OldUser and @NewUser - these are set when you want to replicate permissions from one user to another. To just list what you have, make them both the same value.

That covers basic SQL Server users. But there's one thing missing. What if you have someone connecting via their Windows identification token ?

The following script was developed (to the best of my knowledge) by a site DBA at one of my customer's sites. The variable at the top, @DatabaseFilter needs to be set to the name of the database you're running this against.