Audit Configuration Changes

Do you know the last time a Server Property was changed on your instances of SQL Server?

Are you wondering when the setting for max degree of parallelism was changed?

Do you know who changed the setting?

In some environments there are a lot of hands trying to help mix the pot.  Sometimes more hands can make light work.  This is not always the case though.  More hands in the mix can be a troublesome thing.  Especially when things start changing and the finger pointing starts but nobody really knows for sure who made the change or when the change was made.

I know, that is why there is this concept called change management.  If you make a change to a setting, it should be documented, approved and communicated.  Unfortunately the process does not always dictate the work flow and it may be circumvented.  This is why auditing is a tool that is in place and should be in place – like it or not.

Auditing can be a very good tool.  Like many things – too much of a good thing is not a good thing.  Too much auditing can be more of a hindrance than help.  You don’t want to cause interference by auditing too many things.  You also don’t want too much data that the facts get blurred.  I hope that this script strikes more of a balance with just the right amount of data being captured to be of use.

The basic principle to auditing server configs is to find what values changes, when they were changed and by whom.  There are ancillary details that can be helpful in tracking the source of the change such as the hostname of the source computer making the change.  These are all things that we should capture.  But if a setting hasn’t changed – then we need not necessarily report that the setting was unchanged (it should go without saying).

So for this, I created a couple of tables and then a script that I can add to a job to run on a regular basis.  I can put the script in a stored procedure should I desire.  I’ll leave that to you as an exercise to perform.

USE AdminDB;
GO
SET NOCOUNT ON;
 
DECLARE @ConfigLastUpdateDate	DATETIME
		,@PreviousPollDate		DATETIME
		,@MaxPollDate			DATETIME
		,@PATH					NVARCHAR(260);
 
SELECT @PATH = REVERSE(SUBSTRING(REVERSE([PATH]), 
						CHARINDEX('\', REVERSE([path])), 260)) + N'LOG.trc'
	FROM sys.traces 
	WHERE is_default = 1;
 
IF OBJECT_ID('tempdb..#DBCCConfig') IS NOT NULL DROP TABLE #DBCCConfig;
	CREATE TABLE #DBCCConfig(
		[ParentObject] VARCHAR (100),
		[Object]       VARCHAR (100),
		[Field]        VARCHAR (100),
		[Value]        VARCHAR (100)); 
 
INSERT INTO #DBCCConfig (ParentObject, Object, Field, Value)
	EXECUTE ('DBCC CONFIG WITH TABLERESULTS');
 
WITH cte AS
(
SELECT cfgupddate = MAX(CASE WHEN t1.Field = 'cfgupddate' THEN t1.Value ELSE NULL END),
        cfgupdtime = MAX(CASE WHEN t1.Field = 'cfgupdtime' THEN t1.Value ELSE NULL END)
FROM #DBCCConfig t1
WHERE   Field IN ('cfgupddate', 'cfgupdtime')
)
SELECT  @ConfigLastUpdateDate = CONVERT(DATETIME,t3.configure_upd_dt)
FROM    cte t1
        CROSS APPLY (SELECT cfgupddate = DATEADD(DAY, CONVERT(INT, t1.cfgupddate), '1900-01-01')) t2
        CROSS APPLY (SELECT configure_upd_dt = DATEADD(ms, CONVERT(INT, t1.cfgupdtime)*3.3, t2.cfgupddate)) t3;
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigAudit')
	CREATE TABLE SysConfigAudit (
		configuration_id	int
		,name	NVARCHAR(256)
		,value	sql_variant
		,minimum	sql_variant
		,maximum	sql_variant
		,value_in_use	sql_variant
		,description	NVARCHAR(MAX)
		,is_dynamic	bit
		,is_advanced	BIT
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME)
 
IF NOT EXISTS (SELECT Name 
					FROM sys.objects 
					WHERE name = 'SysConfigChangeLog')
	CREATE TABLE SysConfigChangeLog (
		configuration_id	int
		,name	NVARCHAR(256)
		,CurrValue	SQL_VARIANT
		,PrevValue	SQL_VARIANT
		,description	NVARCHAR(MAX)
		,PollDate		DATE
		,LastConfigUpdtDate	DATETIME
		,PrevConfigUpdtDate	DATETIME
		,ChangeDate	DATETIME
		,ChangeBy	NVARCHAR(256)
		,HostName	NVARCHAR(256)
		,ChangeSPID	SQL_VARIANT
		,Changedata	NVARCHAR(2000)
		,ApplicationName NVARCHAR(256)
		,Severity INT
		,ERROR SQL_VARIANT
		,ChangeBySessionLogin	NVARCHAR(256))
IF NOT EXISTS (SELECT Name 
					FROM sys.key_constraints 
					WHERE name ='PK_SysConfigChangeLog' 
						AND OBJECT_NAME(parent_object_id) = 'SysConfigChangeLog')
BEGIN
ALTER TABLE dbo.SysConfigChangeLog ADD CONSTRAINT
	PK_SysConfigChangeLog PRIMARY KEY CLUSTERED 
	(
	configuration_id,
	ChangeDate DESC
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
		ON Admin_Data
 
END
 
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
/* Write the configurations out to audit table 
*/
IF CONVERT(DATE,GETDATE()) <> @MaxPollDate
BEGIN
	INSERT INTO SysConfigAudit
			( configuration_id ,
			  name ,
			  value ,
			  minimum ,
			  maximum ,
			  value_in_use ,
			  description ,
			  is_dynamic ,
			  is_advanced,
			  PollDate,
			  LastConfigUpdtDate
			)
	SELECT configuration_id,name
			,value
			,minimum,maximum
			,value_in_use
			,description
			,is_dynamic,is_advanced
			,GETDATE(),@ConfigLastUpdateDate
		FROM master.sys.configurations;
END
 
/* Recast MaxPollDate */
SET @MaxPollDate = ISNULL((SELECT TOP 1 PollDate
								FROM dbo.SysConfigAudit 
								ORDER BY PollDate DESC),'1/1/1900')
 
SET @PreviousPollDate = ISNULL((SELECT TOP 1 PollDate 
									FROM dbo.SysConfigAudit 
									WHERE Polldate <> @MaxPollDate 
									ORDER BY PollDate DESC),'1/1/1900');
 
/*	A configuration has changed and a reboot has occurred 
	causing the updtdate to be written to the config block of the page 10
	The configuration may not be written to the page, but we will write it to
	the table anyway and then compare to the default trace file in all cases.
	If there are any values changed, then write those to the change log with
	the login of the person who changed the value
*/
 
BEGIN
 
WITH presel AS (
SELECT df.LoginName,df.TextData,df.StartTime,df.HostName,df.Severity,df.DatabaseName,df.SPID,df.ERROR
		,df.SessionLoginName,df.ApplicationName
		,SUBSTRING(df.TextData
					,CHARINDEX('''',df.TextData)+1
					,CHARINDEX('''',df.TextData
								,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1)
				AS ConfigName
		,ROW_NUMBER() OVER (PARTITION BY SUBSTRING(df.TextData
													,CHARINDEX('''',df.TextData)+1
													,CHARINDEX('''',df.TextData
															,CHARINDEX('''',df.TextData)+1)-CHARINDEX('''',df.TextData)-1) 
								ORDER BY df.StartTime DESC) AS RowNum
	FROM ::fn_trace_gettable( @path, DEFAULT )  df
	WHERE 1=1
		AND df.TextData LIKE '%Configuration option%'
		AND df.ApplicationName NOT IN ('SQLAgent - Initial Boot Probe','SQLAgent - Enabling/disabling Agent XPs')
		AND df.Severity IS NOT NULL
)
 
INSERT INTO SysConfigChangeLog (configuration_id,name,CurrValue,PrevValue,description,PollDate	
							,LastConfigUpdtDate	,PrevConfigUpdtDate	,ChangeDate,ChangeBy
							,HostName,ChangeSPID,Changedata,ApplicationName,Severity
							,ERROR,ChangeBySessionLogin)
SELECT CUR.configuration_id,cur.NAME,cur.VALUE AS CurrValue,Prev.VALUE AS PrevValue,cur.description
		,cur.polldate, cur.LastConfigUpdtDate,prev.LastConfigUpdtDate AS PrevConfigUpdtDate
		,df.StartTime AS ChangeDate,df.LoginName AS ChangeBy,df.HostName,df.SPID AS ChangeSPID
		,df.TextData AS ChangeData,df.ApplicationName,df.Severity,df.ERROR
		,df.SessionLoginName AS ChangeBySessionLogin
	FROM AdminDB.dbo.SysConfigAudit CUR
		INNER JOIN AdminDB.dbo.SysConfigAudit Prev
			ON CUR.configuration_id = Prev.configuration_id
			AND CUR.PollDate = @MaxPollDate
			AND Prev.PollDate = @PreviousPollDate
		CROSS APPLY presel  df
	WHERE df.RowNum = 1
		AND df.ConfigName = CUR.NAME
		AND CUR.VALUE <> Prev.VALUE	
		AND NOT EXISTS (SELECT configuration_id
							FROM SysConfigChangeLog
							WHERE Name = Cur.NAME
								AND CurrValue = CUR.VALUE
								AND PrevValue = Prev.VALUE
								AND ChangeDate = df.StartTime
								AND ChangeBy = df.LoginName)
		;
END
 
SELECT *
	FROM dbo.SysConfigChangeLog
	ORDER BY ChangeDate DESC;

Here I am trapping the config settings on a daily basis (as the script is written for now). I then cross reference the current settings against the previous settings.  Then I check the default trace to see if any of those settings exist in the trace file.

The default trace captures the data related to configuration changes.  On busy systems, it is still possible for these settings to roll out of the trace files.  For those systems, we may need to make some alterations to the script (such as running more frequently and changing the comparisons to account for smaller time intervals than a day break).

To ensure proper comparison between the audit tables and the trace file, note the substring function employed.  I can capture the configuration name and then join to the audit tables on configuration name.

This has proven useful to me so far in tracking who did what to which setting and when they did it.

I hope you will find good use for it as well.

Public Role and Security

Tags: ,
Comments: 1 Comment
Published on: December 20, 2011

Having flown a fair amount lately, I was thinking about the various levels of security within an airport.  Part of that comes from seeing signs like the following all over the place.

 

These signs are placed in various places for very good reason.  But seeing a sign such as this made me wonder about the various access levels in an airport and how they might relate to the database world.

Let’s start with some of the zones that might be recognizable in an airport.  First there is the ticketing counter.  This is a general access area open to the public.  Anybody can approach a ticketing counter whether they are intending to purchase a ticket or not.  Another area similar to this is frequently the baggage claim area.  These are common areas and generally less secure than other areas.

Next, you might encounter the concourses.  Only ticketed passengers and authorized airport personnel may enter these areas after some degree of screening.  From these areas you have greater access to the airplanes.  You have been explicitly granted permission to enter an aircraft and are required to have a separate pass for each craft you desire to board.  If I were to correlate this to security in SQL server, this would most closely match the db_datareader database role – for which explicit permission has to be granted for the user in order to access each additional database.

From this same area you may witness that there are several aircraft crews.  Each member filling a specific role.  There is a pilot, copilot, flight attendants and ground crew.  Each role may have different access throughout the airport.  And in the case of the flight crew, they have to be given access to each plane they will board.  A United Airlines flight crew cannot go and pilot a British Airways craft for instance – they are responsible for specific flights belonging to UA.

Another potential role is that of the control tower.  The personnel manning the tower have access to quite a bit more than a pilot or passenger.  They have access to communications between all flights and the ground within their airspace.  They are coordinating efforts and trying to make the whole thing go smoothly.  These guys are much more like the specialized server roles in SQL Server.  They can be passengers and have public access.  They can also assist in the piloting of a craft (if you believe what you see in the movies) while giving instruction for flight path, landing and takeoff.

The point is, there is highly segregated roles in an Airport and in the air when an aircraft is involved.  The same should be true in a database environment.  There are special server roles that include public, sysadmin, securityadmin, and diskadmin (amongst others).  Then there are specific database roles that come prepackaged as well as the ability to create any number of specific roles that you need to run your environment.

Now let’s step back out again to the airport example and the public access areas.  These are the least secure areas.  Also, there is a group of people that we should call public.  I am a part of this particular group.  Every person that enters an airport is a member of this group.  The crew piloting a craft is a member of this public group, but they are also members of other more restricted groups.

Being a member of just the public group does not get me permission to enter the pilots cabin.  It does not grant me permission to enter the flight control tower.  It does not even grant me permission to stand behind the ticket counter.  You wouldn’t want just any old Joe Schmoe entering those particular areas – so they become more secure.  And the public group is denied access.

Back to the public server role in SQL Server.  This role is granted VIEW Any Database as well as Connect, but by default is limited to just those permissions.  Can that be changed?  Sure – just like I could walk behind the ticket counter or walk into a pilots cabin (the flight staff may occasionally allow you to take a peek – typically children though).

Just because it can be changed – doesn’t mean it should be done.  In the example of me taking a peek into the Pilots cabin, that is a one person permission being granted.  If I granted that permission to the public role in SQL Server, now everybody can do that same thing.  So think about it for a minute, do you really want everybody being able to change the schema in your database if you decide to grant alter any to public?  I really doubt it.

A good rule of thumb with the public role is to leave it be.  Do not add permissions to this role.  Add permissions on a per database  and per group of users basis.  Create roles within the database and grant permissions to that role – in each database.  And remember the rule of least privilege – don’t grant more permissions to a user/role than necessary to perform the job function.  Just the same as in an airport – everybody has their role and it is strictly defined.  If the user need not have access – then don’t grant the permissions.

I want to re-iterate that point.  To help prevent unauthorized access, keep permissions in the public role to a minimum and create roles within the database to manage the different job functions as necessary/possible.

Stored Procedures – Common Security Practice

Tags: , ,
Comments: No Comments
Published on: September 20, 2011

In SQL Server a good practice is to access the data via calls through stored procedure.  Have a look at the document available in that link.

To further this practice, one may create a database role, then add users to that role.  Permissions to execute the stored procedures would then be granted to the role.  A role is simple enough to create.  You can do that with the following code.

CREATE ROLE db_executor

After creating this role, simply add users to that role.  The next part of the process is to ensure that you have granted the appropriate permissions to this role.  There are two methods to do that: 1) blanket execute to all procedures, and 2) pick and choose the procs to which you wish to grant permission.

Before we get to adding permissions, let’s create a little test proc for testing purposes.  I will reuse something from a past article to simplify.  In that article, I already did the setup for the table – you can get it from here.  The stored procedure is as follows.

CREATE PROCEDURE colorwheel
	@ColorType INT = 3
 
AS
SET NOCOUNT ON
BEGIN 
	SELECT cp1.*
		FROM ColorPlate cp1
		WHERE cp1.colortype & @ColorType <> 0 
		ORDER BY ColorID;
END

Let’s also make sure that the user has been added to the role.

EXEC SP_ADDROLEMEMBER 'db_executor', 'testu'

As for the exercise in how to create that database user and the associated login, I will leave that for you to do.

In order to test, we need to connect to the SQL Server as that user.  Once connected, run the following to verify that your session is connected as desired.

SELECT SYSTEM_USER AS LoggedInUser

On my connection, running that query will show that the LoggedInUser is testu.  Now, having confirmed that I am connected as the appropriate user, I will try to execute that test proc we created.

EXEC colorwheel

At this point, the expected results should be similar to this error message.

Msg 229, LEVEL 14, STATE 5, PROCEDURE colorwheel, Line 1
The EXECUTE permission was denied ON the OBJECT 'colorwheel', DATABASE 'TestA', SCHEMA 'dbo'.

Now, I will switch over to the previous connection where I have administrative permissions.  I will now proceed to grant execute permissions following the first method – blanket grant.

GRANT EXECUTE TO db_executor
go

And now, flip back to the user connection to test our permissions.  Try running that proc again, and your results should be similar to these.

ColorID ColorPlate ColorType
1 Red 1
2 Blue 2

That is good, but what else can this user now do?  The user can execute all user created stored procedures.  Do you necessarily want this?  What if your business requirements specify that certain user groups be able to execute only certain procs?

In that case, we now need to grant execute permissions on a more granular level.  A big problem with this method pops up right from the beginning.  What if there are thousands of stored procedures?  What if you need to grant execute permissions to hundreds of stored procedures for each role?

We have two avenues for these types of situations.  One avenue is to separate the various stored procedures via schema and then grant execute to the schema.  The other is via naming convention.

In the event you have a suitable naming convention to help mass assign permissions, here is a little script to help.

SELECT 'Grant Execute on ' + NAME + 'to [db_executor]'
	FROM sys.objects
		WHERE type = 'p'
			AND Name LIKE '%usp_rs_%'

It is very simplistic, I know.  I also left an example of such a naming scheme.  In this example, the naming convention may imply that the procedure is a Reporting Services stored procedure.  I can query for all of the Reporting Services procs in the database, and then assign permissions to all of them much faster.

This method does not immediately grant permissions to the entire result set.  It does allow for you to review the results.

Are there more elaborate examples out there?  Certainly.  Find a method that suits you.  I would be very careful about using the first method though – it just might be too much granted to the user.

Column Level Permissions

Categories: News, Professional, Scripts, SSC
Comments: No Comments
Published on: September 19, 2011

Did you know that you can grant permissions down to the column level in SQL Server?   Well, if you didn’t know that – you do now.

It is actually rather simple to grant permissions at the column level.  This can be demonstrated by the following script.

GRANT SELECT ON ColorPlate (ColorID) TO testU

If you want to check out more on that syntax, read here.

And then…

Why is it important to know that you can do this?  Well, it is quite possible you have some of these permissions already in place.  It is possible you may have inherited something like this.  Just maybe there is a business requirement requiring that certain users or groups only have access to certain data within certain columns.

That brings up a new problem then.  How do you find out what columns have specific permissions applied to certain users?  Well, that is actually pretty straight forward.  We can query the system views and determine column level permissions.

SELECT dp.grantee_principal_id,p.name AS UName
		,dp.permission_name,c.name
		,OBJECT_NAME(o.OBJECT_ID) AS TabName
	FROM sys.database_permissions dp
	INNER JOIN Sys.objects O
		ON dp.major_id = o.OBJECT_ID
	INNER JOIN sys.columns C
		ON c.OBJECT_ID = O.OBJECT_ID
		AND c.column_id = dp.minor_id
	INNER JOIN sys.database_principals P
		ON p.principal_id = dp.grantee_principal_id

The previous query is a really simple version of how to find this information.  As you can see, I am simply returning the UserName, TableName and ColumnName along with the permission in effect on that column.

You should also be able to see that the mapping between these system views is pretty straight forward as well.  Major_id maps to object_id and column_id maps to minor_id.

Conclusion

This query can be of good use to determine permissions in place for columns in tables within your database.  Furthermore, you can even use this query to simply test your curiosity as you check to determine what has been put into effect in the databases you manage.

There are more complex methods to determine these permissions.  With there being more complex methods, I am sure there are also some easier methods.  Let me know what you do to query these permissions.

Security Audit

Comments: 2 Comments
Published on: March 19, 2010

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.

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.

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.

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

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.

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.

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.

Edit: Fixed some WP formatting issues.

page 1 of 1




Calendar
April 2014
M T W T F S S
« Mar    
 123456
78910111213
14151617181920
21222324252627
282930  
Content
SQLHelp

SQLHelp


Welcome , today is Saturday, April 19, 2014