Is there a query that will let me see what SQL users has what access
to which DBs and what lvl access they have?

(click37@gmail.com) writes:
> Is there a query that will let me see what SQL users has what access
> to which DBs and what lvl access they have?

Not a single query, as the login-user mapping for a database is stored
within it. You need to query each database, although this can be packaged
with sp_MSforeachdb. I started to write something, but then it occurred
to me that you had not said which version of SQL Server you are using.
And the solution is completely different for SQL 2000 and SQL 2005.

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.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Aug 21, 5:31 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (clic...@gmail.com) writes:
> > Is there a query that will let me see what SQL users has what access
> > to which DBs and what lvl access they have?
>
> Not a single query, as the login-user mapping for a database is stored
> within it. You need to query each database, although this can be packaged
> with sp_MSforeachdb. I started to write something, but then it occurred
> to me that you had not said which version of SQL Server you are using.
> And the solution is completely different for SQL 2000 and SQL 2005.
>

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.



> --
> 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

(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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

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!