Solved

Permissions for query of db security (audit)

Posted on 2011-03-10
6
232 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

20 Experts available now in Live!

Get 1:1 Help Now