June 18, 2010 at 2:34 pm
Hi everyone,
I've just joined an organization that has not previously had a DBA, but has around 40 instances of SQL.
I'm setting up some basic monitoring to get started, but I also want to do some serious auditing of all the instances, and then maintain tabs on access once the audit has been complete, as adjusting business processes to stop developers requiring SA access is taking much longer than I would have liked.
What I ideally want is some t-sql I can run on any given server, that will list
a) all server roles, and what logins are members of those roles
b) for each database, list all db roles and their members.
I've done some searching on line, and through trial and error I can get lists of roles & users, but I cannot find a relation between the two. Perhaps its just because its Friday afternoon...
Can anybody point me to the direction of tables or views that I can query? I don't need the query written, just a pointer to some tables or views so i can get started.
Thanks & have a great weekend everybody!
June 18, 2010 at 2:51 pm
Ok, so I posted a little too soon:
For the server roles:
sp_helpsrvrolemember
And for the db roles:
sp_helprolemember
cheers, Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply