August 27, 2008 at 8:55 am
Hello all,
I am documenting our servers. And have come to the part of logins and their roles on the server. I was thinking there was a system stored procedure to get this information to keep me from having to go into each server and write down all the logins with their respective roles. I tried googling it but was having trouble finding it. Any suggestions/answers to help me get this information? Also is there way to get this information and with the database they are tied to and their roles on the database as well? Any information will be helpful! Thanks
August 27, 2008 at 9:07 am
You would want to look at some of the system stored procedures to get this information including sp_helpsrvrolemember, sp_helplogins at the server level and sp_helpuser, sp_helprolemember at the database level. You will have to pull that into to some tables and join appropriately to develop a comprehensive view.
You can also search Security Stored Procedures in BOL to get more procedures that will help you view this information.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 27, 2008 at 9:08 am
Do I need to run these sp's on the master db?
August 27, 2008 at 9:14 am
The first two you can run anywhere but they are just information for the instance level security. The second two need to be run in the individual databases.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
August 27, 2008 at 9:20 am
Ok thanks! This was very helpful information!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply