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.

/* 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.