Do you need to track permissions on the SQL server databases that you are responsible for? Are you sure you know everyone who has sysadmin rights, or who can delete records from the production servers? How often should you perform a security audit?

In my case I don’t have to check permissions on a regular basis. But I still want to keep track of who can do what. So I run the following scripts to check both server and database level permissions as well as check the default trace for any new permissions or users I may not be aware of. They also help me easily compare permissions across servers.

I run these scripts on a mix of SQL 2005 and SQL 2008 servers. I haven’t had a chance to run on SQL 2008 R2 or SQL 2012 yet so I’m not guaranteeing that they’ll work there. Also they may return a lot of records, depending on the number of users in your databases. But they can be modified to filter by user. As always, though, please understand the scripts before running them on a production server.

One final note. I used a script I found on the internet a while back as a basis for the ones I’m using now. Unfortunately I don’t remember where I found them so I can’t properly give credit where it’s due.

This script is run against the server and has 2 parts. First it returns a list of server roles and who belongs to which role. The second part returns individual permissions for each account.

-- Server role members
SELECT
sp1.[name]
, sp1.type_desc
, sp2.[name]
, sp2.type_desc
, sp2.create_date
, sp2.modify_date
FROM sys.server_role_members AS srm
INNER JOIN sys.server_principals AS sp1
ON srm.role_principal_id = sp1.principal_id
INNER JOIN sys.server_principals AS sp2
ON srm.member_principal_id = sp2.principal_id
ORDER BY sp1.[name], sp2.[name]

-- Server users
SELECT
sp1.[name]
, sp1.type_desc
, sp1.create_date
, sp1.modify_date
, sp.state_desc AS 'Permission'
, sp.permission_name AS 'Action'
, sp.class_desc
, sc.class_desc
, sp2.[name]
FROM sys.server_permissions AS sp
INNER JOIN sys.server_principals AS sp1
ON sp.grantee_principal_id = sp1.principal_id
INNER JOIN sys.server_principals AS sp2
ON sp.grantor_principal_id = sp2.principal_id
LEFT JOIN sys.securable_classes AS sc
ON sp.major_id = sc.class
ORDER BY sp1.[name], sp1.type_desc

My next script does the same thing on a database level. Just change the name of the database to see who belongs to which database role or other individual rights.

USE <database_name, sysname, master>;

-- Database role members
SELECT
pr1.[name]
, pr1.type_desc
, pr2.[name]
, pr2.type_desc
, pr2.create_date
, pr2.modify_date
FROM sys.database_role_members AS rm
INNER JOIN sys.database_principals AS pr1
ON rm.role_principal_id = pr1.principal_id
INNER JOIN sys.database_principals AS pr2
ON rm.member_principal_id = pr2.principal_id
ORDER BY pr1.[name], pr2.[name]

-- Database users
SELECT
pr.[name]
, pr.type_desc
, pr.create_date
, pr.modify_date
, dp.state_desc AS 'Permission'
, dp.permission_name AS 'Action'
, CASE dp.class
WHEN 0 THEN 'Database::' + DB_NAME()
WHEN 1 THEN ISNULL(s.[name] + '.', '') + OBJECT_NAME(dp.major_id)
WHEN 3 THEN 'Schema::' + SCHEMA_NAME(dp.major_id)
END AS 'Securable'
, pr2.[name]
FROM sys.database_permissions AS dp
INNER JOIN sys.database_principals AS pr
ON dp.grantee_principal_id = pr.principal_id
INNER JOIN sys.database_principals AS pr2
ON dp.grantor_principal_id = pr2.principal_id
LEFT JOIN sys.objects AS o
ON dp.major_id = o.[object_id]
LEFT JOIN sys.schemas AS s
ON o.[schema_id] = s.[schema_id]
WHERE dp.class IN (0, 1, 3, 4)
AND dp.minor_id = 0
ORDER BY pr.[name], pr.type_desc

Finally there’s a way to monitor who is adding users, or changing permissions. Those events are captured by the default trace that runs when the SQL service starts up. My script only checks the latest trace file, but it can be modified to look at older .trc files. If you’ve disabled the default trace then this won’t work.

USE master;

SET NOCOUNT ON;

DECLARE @enable int;
 SELECT TOP 1 @enable = CONVERT(int,value_in_use)
 FROM sys.configurations
 WHERE name = 'default trace enabled';

IF @enable = 1
 BEGIN
 DECLARE @d1 datetime;
 DECLARE @diff int;
 DECLARE @curr_tracefilename varchar(500);
 DECLARE @base_tracefilename varchar(500);
 DECLARE @indx int ;

SELECT @curr_tracefilename = path
 FROM sys.traces
 WHERE is_default = 1;

SET @curr_tracefilename = reverse(@curr_tracefilename);
 SELECT @indx = PATINDEX('%\%', @curr_tracefilename);
 SET @curr_tracefilename = reverse(@curr_tracefilename);
 SET @base_tracefilename = LEFT(@curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc';

SELECT
 t.LoginName
 , t.HostName
 , t.ApplicationName
 , t.StartTime
 , t.DatabaseName
 , t.TargetUserName
 , t.RoleName
 , e.[name] AS EventDesc
 FROM ::fn_trace_gettable(@base_tracefilename, default) AS t
 INNER JOIN sys.trace_events AS e ON t.EventClass = e.trace_event_id
 WHERE t.EventClass BETWEEN 104 AND 114
 END
 

I’d love to hear how you handle security on your servers. I’m always looking for ways to improve my processes.

Like a lot of fellow SQL Server DBA’s I have a number of production servers to manage. One of the tasks I have to perform every year is to create a new set of one-time schedules for an existing job. The date of the schedule varies from month to month due to different business logic. This task would be extremely time consuming if I had to use the SSMS interface to add the new schedules to the jobs. So I’ve prepared a T-SQL script I can run on multiple servers at once.

Here’s the script I use. The parameters are all the options you would set in the SSMS GUI. In my case this is a one time schedule not set to run via a recurring pattern (every Monday for example). I set @active_start_date and @active_start_time to the date and time I want the schedule to run on; in this case it’s set to February 4th 2013 at 8:50 PM.

EXEC msdb.dbo.sp_add_jobschedule
@job_name = 'My Job Name',
@name = 'Jan 2013',
@enabled = 1,
@freq_type = 1,
@freq_interval = 1,
@freq_subday_type = 0,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 1,
@active_start_date = 20130204,
@active_end_date = 99991231,
@active_start_time = 205000,
@active_end_time = 235959

I usually set a year’s worth of schedules at the same time. When I’m done I run the following script to insure that I’ve created the schedules correctly.

DECLARE @job_id UNIQUEIDENTIFIER
SET @job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE name = 'My Job Name')

SELECT name, active_start_date, active_start_time
FROM msdb.dbo.sysschedules
WHERE schedule_id IN
(
SELECT schedule_id
FROM msdb.dbo.sysjobschedules
WHERE job_id = @job_id
)
AND enabled = 1

And that’s all there is to it. I hope this helps you but please, test the script first. Make sure you understand what it’s doing before running in production. If you screw something up I won’t be around to help you out as I plan on winning the next lottery!

Just a quick little post about an issue that pops up occasionally while I slowly work my way back into SQL after my test.

There are a lot of SQL databases servers that have been configured to use Database Mail to send email alerts for failed jobs, query results, alerts, or other tasks that require some kind of notification. Database Mail, introduced with SQL 2005, is the much improved way to send emails, far better than the older SQL Mail option. If you’re not familiar with Database Mail, go read up on it first on Books Online.

However I’ve found that some DBAs that set up Database Mail aren’t familiar with how to troubleshoot it when users complain they haven’t received expected emails from SQL. Actually it’s pretty simple. Microsoft has included some system views in the msdb database to help you out, and a log to view errors.

If you don’t want to use T-SQL you can view the database mail logs from SSMS. Just right click on Database Mail in the Object Explorer and choose View Database Mail Log (of course this is after you’ve configured Database Mail). Here you can see the status of db mail and any emails that didn’t get sent, including the reason for the failure.

You can get the same information by querying msdb.dbo.sysmail_event_log, including filters for event_type and a specific date if you want;

SELECT * FROM msdb.dbo.sysmail_event_log
WHERE event_type = 'error'
AND log_date >= dateadd(d, -1, getdate())

You can use other views to see the content of the emails. SYSMAIL_FAILEDITEMS shows emails that didn’t get sent, SYSMAIL_SENTITEMS shows emails that were sent successfully, and SYSMAIL_ALLITEMS  combines the two. You can filter by the email_id you got from the log, or see all by a date.

SELECT *
FROM msdb.dbo.sysmail_faileditems
WHERE mailitem_id = 56299;

OK. So you’ve found out that there was a problem sending emails through SQL. You’ve looked at the log to find the problem and fixed it. Now you want to resend the failed emails. Couldn’t be easier. Just query one of the msdb views for the emails you want to resend, store relevant fields in variables, then call the sp_send_dbmail procedure passing in the variables. You can use it for a single item, or wrap it in a loop to resend multiple emails. I use a script like the one below.

DECLARE @to        varchar(max)
DECLARE @copy    varchar(max)
DECLARE @title    nvarchar(255)
DECLARE @msg    nvarchar(max)
SELECT @to = recipients, @copy = copy_recipients, @title = [subject], @msg = body
FROM msdb.dbo.sysmail_faileditems
WHERE mailitem_id =  56299
EXEC msdb.dbo.sp_send_dbmail
@recipients = @to
@copy_recipients = @copy
@body = @msg,
@subject = @title,
@body_format = 'HTML';

And that’s it. Simple, no?