December 13, 2004 at 9:03 am
Does anyone have a query that says 'show me all logins where the login is in database role db_owner' or db_datareader or db_datawriter, for that matter?
I can write the query except for the permissions-role part. Do you have to pull that part from <databasename>..SYSPROTECTS, saying WHERE ACTION IN (198,203,207)? Or is there another table that explicitly includes those roles? Thanks
December 13, 2004 at 9:48 am
sp_helprolemember will list the members of a particular role or, if you omit the role parameter, all roles in a database. See BOL.
Greg
Greg
December 13, 2004 at 10:18 am
Are you looking for one database or all? One is easy, all requires some more work to get this to work and return in one result set.
December 13, 2004 at 10:50 am
I'd like it for all databases on the server, but there are only 9, so executing sp_helprolemember 9 times is not a problem. Thanks
December 13, 2004 at 10:59 am
You can use the script below. Save it as a .vbs file. It gives you the username and its permissions. You can edit it to include other roles also. It creates a file security.txt with the output.
dim rs
dim cn
dim fs
set fs = createobject("Scripting.filesystemobject")
set output = fs.createtextfile("security.txt",true)
set cn = createobject("adodb.connection")
set rs = createobject("adodb.recordset")
set rs_new = createobject("adodb.recordset")
cn.open "DRIVER={SQL Server};Server=Server_name;Uid=User_name;Pwd=Password;"
rs.open "select name from sysdatabases",cn
do while not rs.eof
sql = "select b.name,c.name as role from [" & rs.fields(0).value & "].dbo.sysmembers a join [" & rs.fields(0).value & "].dbo.sysusers b on a.memberuid = b.uid " & _
"join [" & rs.fields(0) & "].dbo.sysusers c on a.groupuid = c.uid " & _
"and c.name in ('db_owner','db_datawriter','db_datareader') " & _
"and b.name != 'dbo' "
output.writeline ("Database Name :" & vbtab & rs.fields(0).value )
set rs_new= cn.execute(sql)
do while not rs_new.eof
output.writeline(rs_new.fields(0).value & vbtab & rs_new.fields(1).value)
rs_new.movenext
loop
output.writeline("")
rs.movenext
loop
Good luck.
December 13, 2004 at 11:52 am
Very good. Thx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply