|
|
Posted by Erland Sommarskog on 09/05/07 21:39
(click37@gmail.com) writes:
> SQL 2000. It doesn't need to 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", you need to 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.
>>
>
> I need to 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, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|