469,588 Members | 2,734 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,588 developers. It's quick & easy.

Need help w/ a script

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

Aug 21 '07 #1
2 1358
(cl*****@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, es****@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
Aug 21 '07 #2
On Aug 21, 5:31 pm, Erland Sommarskog <esq...@sommarskog.sewrote:
(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

Sep 5 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Sofia | last post: by
5 posts views Thread by deko | last post: by
6 posts views Thread by mike | last post: by
28 posts views Thread by Randy Starkey | last post: by
5 posts views Thread by news | last post: by
4 posts views Thread by Brie_Manakul | last post: by
14 posts views Thread by mistral | last post: by
4 posts views Thread by Jonathan Wood | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.