Muller Jannie

About myself and everyone else.

SQL Security

I’ve always wanted to care about SQL security but I never really had to get reaaally pedantic about it.

I suppose I’ve been a bit lucky in that the security requirements were taken care of on other levels. In saying that it’s not like I add every account as sysadmin. Data reader\writer and the occasional exec here and there was mostly the norm.

Since I’m on a bit of a security drive at the moment in order to get rid of any SQL logins I decided to write a report!

(I seem to be doing a lot of reports, it’s a pity that SQL the product itself don’t really have DBA reporting.)

Moving on.

I wrote some code that gives you a quick overview of what’s happening on your server. Below is the code, it’s pretty basic. It cycles through each database and log the roles for specific users and then also the permissions which is the individual object access, i.e select on table <x> or exec proc <x> . Then it also gathers the server role information and the server permissions such access to certificates. Note that I have specifically excluded certificate details i.e. encryption details out of the query. So if you use encryption you will need to modify the code.

Code

declare @account varchar(100)
set @account = ‘all’

if object_id(‘tempdb..#Security’) is not null drop table #Security
create table #Security(Account varchar(255),Scope varchar(50),Permission varchar(255),[Database] varchar(255),Object varchar(255))

insert into #Security
exec sp_msforeachdb ‘use [?]
select
a.name as Account,
”Database” as Scope,
d.name as Permission,
db_name() As [Database],
”” As Object
from sys.server_principals a
inner join sys.database_principals b
on a.sid = b.sid
inner join sys.database_role_members c
on b.principal_id = member_principal_id
inner join sys.database_principals d
on d.principal_id = c.role_principal_id’

insert into #Security
exec sp_msforeachdb ‘use [?]
select
a.name as account,
”Database” as Scope,
Permission_name as Permission,
db_name() as [database],
case when (class_desc = ”Database”) then (””) else (object_name(major_id,db_id())) end as Object

from sys.server_principals a
inner join sys.database_principals b
on a.sid = b.sid
inner join sys.database_permissions e
on e.grantee_principal_id = b.principal_id’

insert into #Security
select
a.name as Account,
‘Server’ as Scope,
isnull(d.name,’Public’) as Permission,
” As [Database],
isnull(Permission_name,”) As [object]
from sys.server_principals a
left join sys.server_role_members c
on a.principal_id = c.member_principal_id
left join sys.server_principals d
on d.principal_id = c.role_principal_id
left join sys.server_permissions e
on a.principal_id = e.grantee_principal_id
where a.type in (‘S’,’G’,’U’,’K’,’C’)

select Account,Scope,Permission,[Database],Object,1 as count from #security where account like case when (@account=’all’) then (‘%’) else (@account) end

I’ve been meaning to write this query for some time and since I’ve done it. Security is less of a guess. By the way you can also use this query to generate some dynamic SQL which allows you to script logins or transfer \ duplicate access between users.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Information

This entry was posted on November 15, 2012 by in Awesome +1, SQL Server and tagged , .