Of late I have seen a lot of questions on how to audit the logins and users on each SQL Server. I had the same questions for myself when I went through the same exercise some time ago. My first step was to peruse the internet and see what I could find to get me started. I found that to be quite helpful. I found a lot of different scripts that were beneficial. I, like most, did find one though that I preferred above the rest. That script can be found here.
Why do I like this script? I like the format. It also generates a nice output that can be passed along to auditors. The output is saved into an html format and seems more presentable to me. Besides those facets, it meets the base requirements – I can find what roles and users have what permissions in each database on a SQL Server Instance.
The script didn’t quite suit all of my needs. I think that is frequently the case. The trick is being able to take the script and make necessary adjustments to suit whatever needs you may encounter. The changes that I made to this script were in favor of progressing toward an automated inventory solution that I could run from a central location. The script as it stood required manual intervention. Granted, I have not yet completed my inventory solution, I have modified the script to work well with 2000 and 2005 and output the results to a properly consumable html file. Since 2000 and 2005 behave differently in certain regards, I had to add some logic for the script to also behave differently if depending on the version of SQL Server it was run against. This was necessary since I have SQL 2000 – SQl 2008 in my environment.
Scripts of Change
So, starting from the top. I decided to use several more variables and create a bunch of temp tables. The variables will help in the decision making, and the temp tables will help in Data storage for processing as the script runs. Thus we have this block of code at the top in place of the old Variable block from the original script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
DECLARE @i INT ,@rc INT ,@dbname VARCHAR(400) ,@MajorProdVers TinyInt ,@DMViewExists TinyInt ,@ServerName Varchar(64) ,@ConfigValueAdv Char(1) ,@ConfigValueX Char(1) ,@ConNameX VarChar(100) ,@ConNameAdv VarChar(100) ,@SQL VarChar(1500) -----------------Create Temp Tables-------------------- Create Table #SysLogins (RowNumber INT Primary Key IDENTITY(1,1),Name Varchar(128),DBName Varchar(128),Language Varchar(128) ,IsDenied Char(10),IsWinAuthentication Char(10),IsWinGroup Char(10),CreateDate DateTime,UpdateDate DateTime,ServerRoles Varchar(128)) CREATE TABLE #LoginMap (LoginName VARCHAR(200), UserName VARCHAR(200) NULL) CREATE TABLE #RoleUser (RoleName VARCHAR(200), UserName VARCHAR(200) NULL) CREATE TABLE #ObjectPerms (RowNumber INT Primary Key IDENTITY(1,1), UserName VARCHAR(50), PerType VARCHAR(10),PermName VARCHAR(30), SchemaName VARCHAR(50) ,ObjectName VARCHAR(100), ObjectType VARCHAR(20), ColName VARCHAR(50), IsGrantOption VARCHAR(10)) CREATE TABLE #DatabasePerms (RowNumber INT Primary Key IDENTITY(1,1),UserName VARCHAR(50),PermType VARCHAR(20),PermName VARCHAR(50),IsGrantOption VARCHAR(5)) CREATE TABLE TBLHTML (RowNumber INT Primary Key IDENTITY(1,1),HTML Varchar(2000)) CREATE TABLE #Config (RowNumber INT Primary Key IDENTITY(1,1),CName Varchar(50),Minimum TinyInt,Maximum TinyInt ,ConfigValue TinyInt,RunValue TinyInt) |
That is the prep setup so we can now begin the true work of the script. As, I said there was some decision logic added to the script. I needed to find a way to determine SQL Server version and based on version execute a different script. And now we have the decision block.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Select @MajorProdVers = @@MICROSOFTVERSION / 0x01000000, @ServerName = @@servername IF @MajorProdVers < 9 Begin Set @DMViewExists = 0 End Else Begin Set @DMViewExists = 1 Insert Into #Config (CName,Minimum,Maximum,ConfigValue,RunValue) exec sp_configure 'show advanced options' Insert Into #Config (CName,Minimum,Maximum,ConfigValue,RunValue) EXEC sp_configure 'xp_cmdshell' End |
Basically, I am checking the version and determining if I should use the SQL 2000 objects or if I can use the SQL 2005 objects since the 2000 objects are scheduled for deprecation. Also, since xp_cmdshell is disabled by default in SQL 2005, I am prepping to enable that just for the final piece of this script. Due to the nature of xp_cmdshell, it is advisable that you understand the security risk involved and revert it back to disabled – if you enabled it to run this script. There are other methods for doing this, I am sure, but I chose this since I got consistent results and have not had time to revisit it.
After that decision tree, I have changed the main body of the script to also use a decision tree in building the dynamic sql. That tree is built like the following snippet.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 |
----------------Database level Permissions------------------------- If @DMViewExists = 0 Begin EXEC ('INSERT INTO #DatabasePerms (UserName,PermType,PermName,IsGrantOption) SELECT usr.Name ,CASE perm.protecttype WHEN 204 THEN ''With Grant'' WHEN 205 Then ''Grant'' ELSE ''Deny'' END As PermType ,CASE perm.action WHEN 26 THEN ''References'' WHEN 178 THEN ''Create Function'' WHEN 193 THEN ''SELECT'' WHEN 195 THEN ''INSERT'' WHEN 196 THEN ''DELETE'' WHEN 197 THEN ''UPDATE'' WHEN 198 THEN ''Create Table'' WHEN 203 THEN ''Create Database'' WHEN 207 THEN ''Create View'' WHEN 222 THEN ''Create Procedure'' WHEN 224 THEN ''Execute'' WHEN 228 THEN ''Backup Database'' WHEN 233 THEN ''Create Default'' WHEN 235 THEN ''Backup Log'' WHEN 236 THEN ''Create Rule'' END As PermName ,CASE perm.action WHEN 204 THEN ''X'' ELSE ''--'' END AS IsGrantOption FROM ['+@dbname+'].dbo.sysprotects AS perm INNER JOIN ['+@dbname+'].dbo.sysusers AS usr ON perm.uid = usr.uid WHERE perm.id = 0 ORDER BY usr.name, perm.action ASC, perm.protecttype ASC' ) End Else Begin EXEC ('INSERT INTO #DatabasePerms (UserName,PermType,PermName,IsGrantOption) SELECT usr.name ,CASE WHEN perm.state <> ''W'' THEN perm.state_desc ELSE ''GRANT'' END As PermType ,perm.permission_name ,CASE WHEN perm.state <> ''W'' THEN ''--'' ELSE ''X'' END AS IsGrantOption FROM ['+@dbname+'].sys.database_permissions AS perm INNER JOIN ['+@dbname+'].sys.database_principals AS usr ON perm.grantee_principal_id = usr.principal_id WHERE perm.major_id = 0 ORDER BY usr.name, perm.permission_name ASC, perm.state_desc ASC' ) End |
I think you can see at this point some of the differences and why I chose to do it this way. The final section of code change comes at the end of the script. This is where the html file is finally built, and then saved out to the file-system.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
Select @ConfigValueX = IsNull(ConfigValue,0),@ConNameX = CName From #Config Where CName = 'xp_cmdshell' Select @ConfigValueAdv = IsNull(ConfigValue,0),@ConNameAdv = CName From #Config Where CName = 'show advanced options' --Enable xp_cmdshell, if disabled, for the duration of this process only. If @ConfigValueAdv = 0 And @DMViewExists = 1 Begin -- To allow advanced options to be changed. Set @SQL = 'EXEC sp_configure ' + '''' + @ConNameADV + '''' + ',' + '''1''' + ';' Print (@SQL) Exec(@SQL) -- To update the currently configured value for advanced options. Set @SQL = 'RECONFIGURE With OVERRIDE;' Print (@SQL) Exec (@SQL) Set @SQL = '' End If @ConfigValueX = 0 And @DMViewExists = 1 Begin -- To enable the feature. Set @SQL = 'EXEC sp_configure ' + '''' + @ConNameX + '''' + ',' + '''1''' + ';' Print (@SQL) Exec (@SQL) -- To update the currently configured value for this feature. Set @SQL = 'RECONFIGURE With OVERRIDE;' Print (@SQL) Exec (@SQL) Set @SQL = '' End -- Clean out the DNS Cache, just in case there is residual bad information. exec master..xp_cmdshell "ipconfig /flushdns" exec master..xp_cmdshell "ping hostname" |
In this section, I am enabling xp_cmdshell if necessary. I am also performing one more necessary trick. I am using xp_cmdshell to flush bad dns records and ping a remote host. I will be saving the file off to a central repository and found some bad dns records on my servers while doing this process. By adding this step, I saved myself quite a bit of frustration in the long-haul. After that, I use xp_cmdshell to bcp the results out to file.
|
1 2 3 4 |
Set @SQL = 'bcp "Select HTML From TBLHTML Order By RowNumber Asc" queryout "\\YourComputer\PermsAudit\' + replace(@ServerName,'\','.') + '.htm" -T -c -q -S' + @ServerName EXEC master..xp_cmdshell @SQL |
This took some work to get the ” ‘ ” all lined up correctly and working properly with BCP. It was somewhat satisfying when it finally came together.
Now, remember I said you should reset xp_cmdshell back to disabled once completed? Well, I built that into the script as a part of the cleanup. I perform this action right before dropping all of those tables that I created.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
If @ConfigValueX = 0 And @DMViewExists = 1 Begin -- To enable the feature. Set @SQL = 'EXEC sp_configure ' + '''' + @ConNameX + '''' + ',' + '''' + @ConfigValueX + '''' + ';' Print (@SQL) Exec (@SQL) -- To update the currently configured value for this feature. Set @SQL = 'RECONFIGURE With OVERRIDE;' Print (@SQL) Exec (@SQL) Set @SQL = '' If @ConfigValueAdv = 0 Begin -- To allow advanced options to be changed. Set @SQL = 'EXEC sp_configure ' + '''' + @ConNameADV + '''' + ',' + '''' + @ConfigValueADV + '''' + ';' Print (@SQL) Exec(@SQL) -- To update the currently configured value for advanced options. Set @SQL = 'RECONFIGURE With OVERRIDE;' Print (@SQL) Exec (@SQL) Set @SQL = '' End End |
Conclusion
I effectively took a well working script and made it suit my needs / wants just a little better. The initial code was just over 300 lines and I nearly doubled that with this script. Is it worth the extra effort? Yes! Though it took some time and effort to make these modifications, I was able to finish auditing the servers well ahead of pace of doing it by hand.
Furthermore, I can still use this script and continue to reap the benefits of having taken the time to modify it. Can the script be improved? Sure it can. I have a few things in line for it currently. The biggest piece of it will be modifying it to be run from the inventory package I am still trying to finish in my spare time.
You can download the script in its entirety here. You may also read more about security and permissions from these articles here.
Edit: Fixed some WP formatting issues.
This script provides some incredibly detailed information and is extremely valuable. Any chance you can update it to include databases with mirroring? It fails if mirroring is setup anywhere.
I will work on that. Thanks for the suggestion.
Very nice report, good work!
fyi, I needed to add “database.dbo.” before TBLHTML to get the bcp to work.
Set @SQL = ‘bcp “Select HTML From database.dbo.TBLHTML Order By RowNumber Asc”
Thanks for the feedback. I will go test some more. I hadn’t run into the bcp not working.
Hi
Did you ever get around to(time) to update this script?
Thanks for this excellent script. Saved me some time indeed.
I ran into several problems and in case it might help someone else:
When exporting to .htm
1 – Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name ‘TBLHTML’. As I have several instance of sqlserver on my computer, I need to use the fully qualified name of the table
DECLARE @schema varchar(150) = (SELECT OBJECT_SCHEMA_NAME(OBJECT_ID(‘TBLHTML’)));
DECLARE @fullyqualifiedname varchar(150) = QUOTENAME(DB_NAME())+’.’+QUOTENAME(@schema)+’.’+QUOTENAME(‘TBLHTML’);
Set @SQL = ‘bcp “Select HTML From ‘+@fullyqualifiedname+’ Order By etc….
2 – the .htm didn’t have the same content as the ssms output window. 4 databases were missing in the htm file. All 4 databases uses special character (anti-slash to be precise) in their name. I suspect that’s the reason. Haven’t come up with something yet but should not be too complicated.
Just my two cents.
I must correct my last comment : When exporting to .htm
2 – the .htm didn’t have the same content as the ssms output window. 4 databases were missing in the htm file. All 4 databases uses special character “:” (colon) in their name.
the correction was simply
Select ‘ Database ‘ + replace(@dbname,’:’,’:’) + ‘‘
Should not have posted my first comment in a hurry…
oops again. forgot to escape the escape character (if you see what i mean…)
Select ‘ Database ‘ + replace(@dbname,’:’,’:’) + ‘‘