|
|
Posted by click37 on 09/06/07 14:35
On Sep 5, 5:39 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (clic...@gmail.com) writes:
> > SQL 2000. It doesn'tneedto be a stored prod, a developer that I
> > know created a tool that will allow me to run a query across all
> > servers & DBs. .
>
> >> As for "what access" and "what lvl", youneedto be more specific. The
> >> permission scheme in SQL 2005 is very fine-grained, and the query could
> >> be very complex - as could the output be.
>
> > Ineedto know if each user has dbo rights, db_reader/writer and so
> > forth.
>
> Here are two queries. The first gives you role membership in a database,
> the second gives you permissions granted to objects. As for the column
> action, look up what the numbers mean in the description of the system
> table sysprotects in Books Online.
>
> SELECT login = l.name, [User] = u.name, Role = g.name
> FROM sysusers u
> LEFT JOIN master..syslogins l ON u.sid = l.sid
> JOIN sysmembers m ON m.memberuid = u.uid
> JOIN sysusers g ON m.groupuid = g.uid
> ORDER BY User, Role
>
> SELECT login = l.name, [User] = u.name, object = o.name,
> action = p.action
> FROM sysusers u
> LEFT JOIN master..syslogins l ON u.sid = l.sid
> JOIN sysprotects p ON u.uid = p.uid
> JOIN sysobjects o ON p.id = o.id
> WHERE p.protecttype IN (204,205)
> AND o.type <> 'S'
> ORDER BY User, object, action
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thank you sir! They worked like a charm and you saved me from having
to go thru every database :-). I owe u a coke!
Navigation:
[Reply to this message]
|