SQL User Audit report

Question

Post reply

SQL User Audit report

Abdul Wahab-440199

SSC Journeyman

Points: 98
More actions
June 7, 2009 at 1:21 am

#210334

Is there any way to export the users with their assigned roles on a particular SQL Server for auditing purposes?

I tried to find any third party utilities but to no luck.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

bodhilove SSCarpal Tunnel Points: 4035 More actions · Answer 1

I did this once with sql to check my permission and logins transferred correctly from one server to another.

SET @mySQL = 'select [name] from [' + @InstanceName + '].[master].[sys].[databases] where [name] not in (''master'', ''msdb'', ''model'', ''RA50'') and [name] not like ''websense%'' and [name] not like ''ReportServer$%'' '

insert into #dbname([TABLE_CATALOG])

exec (@mySQL)

DECLARE mydbs CURSOR FOR select * from #dbname

OPEN mydbs

FETCH NEXT FROM mydbs INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

SET @mySQL =

'select ''' + @dbname +''', convert(char(45),sp.name) as srvLogin, convert(char(45),sp2.name) as srvRole,

convert(char(50),dbp.name) as dbUser, convert(char(25),dbp2.name) as dbRole from

['+ @instancename +'].[master].sys.server_principals as sp join ['+ @instancename +'].[' + @dbname +'].[sys].[database_principals] as dbp on sp.sid=dbp.sid join ['+ @instancename +'].[' + @dbname + '].[sys].[database_role_members] as dbrm on dbp.principal_Id=dbrm.member_principal_Id join ['+ @instancename +'].['

+ @dbname + '].[sys].[database_principals] as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join ['+ @instancename +'].[master].sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join ['+ @instancename +'].[master].sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id'

insert into #perdata

exec (@mysql)

SET @mysql =

'select ''' + @dbname + ''', convert(char(45),sp.name) as srvLogin,

convert(char(45),sp2.name) as srvRole, convert(char(50),dbp.name) as dbUser, ''public''

from ['+ @instancename +'].[master].[sys].[server_principals] as sp join ['+ @instancename +'].[' + @dbname + '].[sys].[database_principals] as dbp on sp.sid=dbp.sid left join ['+ @instancename +'].[master].sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join ['+ @instancename +'].[master].sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id where not exists (select * from ['+ @instancename +'].[' + @dbname + '].[sys].[database_role_members] as dbrm join ['+ @instancename +'].[' + @dbname + '].[sys].[database_principals] as dbp2 on dbrm.role_principal_id=dbp2.principal_id where dbp.principal_Id=dbrm.member_principal_Id) '

insert into #perdata

exec (@mysql)

FETCH NEXT FROM mydbs INTO @dbname

END

CLOSE mydbs

DEALLOCATE mydbs

select * from #perdata