June 7, 2009 at 1:21 am
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.
June 9, 2009 at 8:02 pm
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
June 10, 2009 at 3:17 am
I do not want to transfer the access anywhere, i just want to report or probably export it to excel in a easy format;
June 10, 2009 at 10:48 pm
in management studio you should be able to save query results as a csv file by right clicking on the results. This should be accessible in excel.
June 11, 2009 at 1:01 pm
Idera has a free tool called SQL Permissions.
Tim White
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy