Re: how to get all the user permission in 1 query?

SQL apprentice (mssqlworld@xxxxxxxxx) writes:
I am trying to write a detailed report of all my users and roles
permission for a database. This is solely for reporting and auditing of
the database users and their permission. I am just trying to write a
SELECT statement with probably an Union All. For example, the result
would like this..(it would be nice if the code works on SQL 2K5 too)

The solutions in this case would be different for SQL 2000 and SQL 2005.
In SQL 2005 the old system tables have been downgraded to compatibility
views, and the recommended way to read metadata is the new catalog
views. Furthermore, there are significant changes to the security model
in SQL 2005, which queries written for SQL 2000 may not be table to
catch.

-- in here, I am looking for the user and its permission to
tables,views,etc..
ObjectName UserName Permission
OrderTable JohnB Select
OrderTable JohnB Write
InventoryTable MikeH dbo

-- in here, I am looking for the role and its permission
ObjectName RoleName Permission
InsertDataPrc DataOwner Execute

These two parts can be written with a single query, since roles
and users are both defined in the table sysusers. The object permissions
are in syspermissions.

-- in here, I am looking for the role and users assign to the role.
RoleName UserName
DataOwner JohnB

And this can be sorted out with a query over sysusers and sysmembers.

I'm not supplying in queries, as I do not have any in store, and I'm
feeling lazy. Instead, I recommend you to look up the tables I've
mentioned in Books Online.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: distribution clean up job fail
    ... The Administrator Account or whatever account the distribution agent is ... running under in sql 2005, or whatever account SQL Server agent is running ... permission on the share and underlying files and folders for this to work. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • RE: Run a ssis package on SQL Server 2005 Error
    ... I can run the SSIS package succesfully in BIDS under the same user as SQL ... The user is a domin user and it has full permission on the remote server, ... SQL Server 2005 and Agent are run under Domin\xxxxx, ...
    (microsoft.public.sqlserver.dts)
  • RE: Keeping User From Deleting Table Records
    ... a regular user, if you don't grant DELETE permission, he cannot delete. ... authentication and authenticate with Windows. ... Windows or is this SQL-assigned read permission done through SQL Server? ...
    (microsoft.public.sqlserver.security)
  • Re: User ASPNET in SQL Server 2000
    ... You need to disable anonymous access, enable Windows access, create group in Windows, assign permissions to that group in SQL, put ... > as the windows user then I want to logon to the SQL Server ... > now I want a permission to the SQL Server 2000 database ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: How Can I Create A "Reader" Role?
    ... You need to explicitly DENY this permission to avoid ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.security)