"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.
/* Security Audit Script - SQL Server 2005 & 2008 Compatible */
USE master
GO
SET NOCOUNT ON;
/***************SPECIFY DATABASE HERE*****************/
DECLARE @DatabaseFilter varchar(255)
SET @DatabaseFilter = 'myprod1'
/* Report Date & Time */
SELECT CONVERT(varchar, GETDATE(), 107)+' @ '+
LEFT(CONVERT(varchar, GETDATE(), 108),5) AS [Report Run Date-Time]
/* Windows Logins - ALL */
SELECT a.name, a.sid, a.type_desc
INTO #server_logins
FROM sys.server_principals a
LEFT JOIN sys.sql_logins b ON b.sid = a.sid
WHERE a.type IN ('U','G')
/* Find Windows Logins w/ Server-Wide Roles */
SELECT d.name AS [Server Role], a.name AS [Server Login],
a.type_desc AS [Logon Type]
FROM #server_logins a
LEFT JOIN sys.server_principals b
ON b.sid = a.sid
LEFT JOIN sys.server_role_members c
ON c.member_principal_id = b.principal_id
LEFT JOIN sys.server_principals d
ON d.principal_id = c.role_principal_id
WHERE d.name IS NOT NULL
ORDER BY a.name
/* Database List */
CREATE TABLE #databases
(row_id int identity(1,1), [name] varchar(255), id int)
INSERT INTO #databases ([name], id)
SELECT DISTINCT a.name, a.database_id
FROM sys.databases a
WHERE a.database_id > 4 --Skip System Databases
AND DATABASEPROPERTYEX(a.[name],'Status') = 'ONLINE' --Online Only
AND a.name = @DatabaseFilter --Single Database Filter
/* Looking for Database Users */
CREATE TABLE #database_logins
([Database] varchar(255),
[Database User] varchar(255),
[Database Role] varchar(255),
[Server Login] varchar(255))
DECLARE @counter int, @max_rows int, @database varchar(255)
SELECT @counter = 1, @max_rows = COUNT(*) FROM #databases
WHILE @counter <= @max_rows
BEGIN
SELECT @database = [name] FROM #databases WHERE row_id = @counter
EXEC('INSERT INTO #database_logins
SELECT '''+@database+''' [Database],
b.name AS [Database User],
c.name AS [Permission],
IsNULL(d.name,''ORPHANED'') AS [Server Login]
FROM ['+@database+'].sys.database_role_members a
LEFT JOIN ['+@database+']..sysusers b
ON b.uid = a.member_principal_id
LEFT JOIN ['+@database+'].sys.database_principals c
ON c.principal_id = a.role_principal_id
LEFT JOIN #server_logins d ON d.sid = b.sid
WHERE b.name <> ''dbo'' AND b.issqlrole = 0')
SET @counter = @counter + 1
END
SET @counter = 1
WHILE @counter <= @max_rows
BEGIN
SELECT @database = [name] FROM #databases WHERE row_id = @counter
EXEC('INSERT INTO #database_logins
SELECT '''+@database+''' [Database], a.name AS [Database User],
''Object-Based'' AS [Database Role],
ISNULL(b.name,''ORPHANED'') AS [Server Login]
FROM ['+@database+']..sysusers a
LEFT JOIN #server_logins b
ON b.sid = a.sid
AND b.name COLLATE DATABASE_DEFAULT =
a.name COLLATE DATABASE_DEFAULT
WHERE a.name NOT IN (''public'',''dbo'',''guest'',
''INFORMATION_SCHEMA'',''sys'',''db_owner'',''db_accessadmin'',
''db_securityadmin'',''db_ddladmin'',''db_backupoperator'',
''db_datareader'',''db_datawriter'',''db_denydatareader'',
''db_denydatawriter'')
AND a.issqlrole = 0 AND a.name NOT IN (
SELECT b.name
FROM ['+@database+'].sys.database_role_members a
LEFT JOIN ['+@database+']..sysusers b
ON b.uid = a.member_principal_id
LEFT JOIN ['+@database+'].sys.database_principals c
ON c.principal_id = a.role_principal_id
LEFT JOIN #server_logins d
ON d.sid = b.sid
WHERE b.name <> ''dbo'')')
SET @counter = @counter + 1
END
SELECT a.[Database], a.[Database User], a.[Database Role]
FROM #database_logins a
ORDER BY a.[Database], a.[Database User], a.[Database Role]
/* Taking out the trash */
DROP TABLE #server_logins
DROP TABLE #databases
DROP TABLE #database_logins
Try it out, see what you think.
