Solved

Permissions for query of db security (audit)

Posted on 2011-03-10
6
233 Views
Last Modified: 2012-05-11


I want to allow a user to be able to view security information for ALL user databases within a SQL Server Instance (2005).

If I create a login on the instance and add it to the sysadmin role, I can use the following query,
substituting in any valid database name on the instance where you see <dbname> to get the information I need.

use  <dbname>
SELECT UserName = dp.name, UserType = dp.type_desc, LoginName = sp.name, [desc] = sp.type_desc FROM sys.database_principals dp JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id
UNION SELECT p.name as UserName, p.type_desc as UserType, pp.name as LoginName, pp.type_desc as [desc] FROM sys.database_role_members roles JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id
order by UserName asc

This works.  However, I do not want to give the login sysadmin privileges.  
It seems that the login would only need select privileges on the three system views at the database level, but that doesn't appear to be enough.
Also, I'd really rather not have to administer things at the database level (e.g. create a user in each database vs. creating the login on the instance and assigning it to a server role)   Databases come and go, so I wouldn't want to have to manually add permissions every time a new database is added.

I'm trying to determine the minimum privileges that are required to execute this query so that it will work against any of the databases on the instance and return the information for ALL users.

Any help would be greatly appreciated.

Thanks.
0
Comment
Question by:web-dba
  • 4
  • 2
6 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 35099015
You can use the following to determin/assign only the right you need to grant to the user:

--at db level:
EXEC sp_helpdbfixedrole
EXEC sp_dbfixedrolepermission 'db_securityadmin'

--at server level:
EXEC sp_helpsrvrole
EXEC sp_srvrolepermission 'securityadmin';
0
 

Author Comment

by:web-dba
ID: 35189411
Thanks for the reply, but I'm trying to query the security info for all databases on the fly.  So I can't add permissions at the db level.  Is there a specific permission I can apply at the server level without giving up the full privileges of sysadmin?
0
 

Author Comment

by:web-dba
ID: 35189470
Clarification - I can set server level permissions and also I can add specific permissions for the system databases.  But user databases are added on the fly and I want to be able to view the security info for all databases, even ones that are newly created, without adding specific permissions at the database level. Thanks.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 39

Expert Comment

by:lcohan
ID: 35201163
<<But user databases are added on the fly and I want to be able to view the security info for all databases, even ones that are newly created, without adding specific permissions at the database level.>>

For that I would add DDL triggers and query the DDL audit tables or use SQL Dashboards reports that come free from Microsoft.

DDL triggers:
DDL_SERVER_LEVEL_EVENTS
http://msdn.microsoft.com/en-us/library/ms186582(SQL.90).aspx
DDL_AUTHORIZATION_SERVER_EVENTS
http://msdn.microsoft.com/en-us/library/ms180671(SQL.90).aspx
DDL_GDR_SERVER_EVENTS
http://msdn.microsoft.com/en-us/library/ms186418(SQL.90).aspx

SQL reports - open your SSMS, connect to the SQL server where you want to see all schema changes then right click the server name, select Reports - > Standard Reports -> Schema Change History
0
 

Accepted Solution

by:
web-dba earned 0 total points
ID: 35722540
Thanks for the suggestions.  None of them really allowed me to do what I was trying to do...I'm going to look for a different approach.
0
 

Author Closing Comment

by:web-dba
ID: 35759452
Thanks for the suggestions.  None of them really allowed me to do what I was trying to do...I'm going to look for a different approach.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now