| Author |
Topic |
|
ctyler72
Starting Member
6 Posts |
 Posted - 2009-01-28 : 14:40:28
|
| I just took over as DBA and I can not seem to find an easy way to list all my users and thier perspective roles across my databases. I have several DBs across several servers. Managment wants me to review security and possibly revoke some roles. I havent been able to even find a script to help. Could anyone help.Thanks, Chris |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 14:41:47
|
| Did you search in forum ? |
 |
|
|
ctyler72
Starting Member
6 Posts |
Posted - 2009-01-28 : 15:00:43
|
| I did some searching but had no luck, that is why I am posting this. |
|
|
|
revdnrdy
Posting Yak Master
220 Posts |
Posted - 2009-01-28 : 15:35:52
|
| Is this what you need?If you have SQL Management Studio just run this stored procedure.exec sp_helpuserYou may also wish to try this one..exec sp_helploginsI did a simple search on google awhile back and easily found it ; )r&r |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 15:48:16
|
| Use my script:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=111918 |
|
|
|
ctyler72
Starting Member
6 Posts |
Posted - 2009-01-28 : 15:50:02
|
| Thanks, I had the sp_helpuser but i wasnt aware of the sp_helplogins. The sp_helplogins will work. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 17:35:11
|
| Did you use my script? |
|
|
|
ctyler72
Starting Member
6 Posts |
Posted - 2009-01-28 : 17:38:28
|
| I tried it out... The issue is that I have several DBs on a server and I need to know users and roles. I dont want to have to run for each db.. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 18:00:31
|
| Use this one for detailed info:http://www.sqlservercentral.com/scripts/Administration/63841/ |
|
|
|
ctyler72
Starting Member
6 Posts |
Posted - 2009-01-28 : 18:06:56
|
| Thanks! That is exactly what I need! that pulled back what I needed. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 18:10:57
|
Welcome  . I have saved that script as well for my use. |
|
|
|
ctyler72
Starting Member
6 Posts |
Posted - 2009-01-28 : 18:18:48
|
| Its a nice script. We are moving toward SAS70 compliance and security is going to be a big issue. I am the first SQL DBA we are mainly an Oracle shop. Prior to me they had a developer, a sys admin, and customer support person managing the SQL servers. So just about everyone and thier brother have sys admin... LOL |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-01-28 : 18:28:00
|
| You can use my as well tfountain's script to track it down in server-level and object-level. |
|
|
|
|
Listing User Roles