Hi,
I have a database and I want let to some role the permission to query all info from the sys.databaseprincipals and see other user names. How can I do this?
Thanks.
+1
A:
Wrap the call in a stored proc or table valued function and use EXECUTE AS OWNER (assuming dbo.nameofcodeobject).
Otherwise, you have to switch off MetaData Visibility protection for the entire server
You can't use EXECUTE AS for views which would be useful here...
Edit, based on comment.
From sys.database_principals:
In SQL Server 2005 and later versions, the visibility of the metadata in catalog views is limited to securables that a user either owns or on which the user has been granted some permission. For more information, see Metadata Visibility Configuration.
- dbo owns everything so sees everything
- Permissions can not be granted because there is no "GRANT VIEW SECURITY"
gbn
2009-03-05 15:55:32
The problem is that I need to access to the sys.database_principals as the dbo sees it through a view.
xgoan
2009-03-05 15:57:16
dbo can see all data anyway... for other users/roles you'll need to wrap it.
gbn
2009-03-05 16:04:30
If I give SELECT permission to the role/user in the view that calls sys.database_principals only shows the users seen by this user.
xgoan
2009-03-05 16:08:25
I'm not saying change permissions. I'm saying wrap the sys call.
gbn
2009-03-05 16:11:26
A:
Maybee its just me and my servers setup but I am able to query the sys.database_principals so long as I have the connect permission. I am also able to see the user name.
You can grant Connect by doing:
GRANT CONNECT TO [USER]
JoshBerke
2009-03-05 16:07:55
You'll only see the users (eg NT groups) that you are associated with. The same applies to sys.server_principals. db_owner and other roles have extended rights
gbn
2009-03-05 16:12:33
Nope I am connecting as a SQL User who only has the connect permission. Hmm let me add another user and see if I see him
JoshBerke
2009-03-05 16:16:50
Ok I see now. I can see all the roles and dbo guest etc...but not the other user.
JoshBerke
2009-03-05 16:19:31
Yep well I was logging in with my least privllaged acount, which only has connect. Kind of odd...also when you look at sp_helptext for the view and try and run it it doesnt work complains about functions and then the tables themselves are invalid. I have a lot of catching up on SQL Internals...
JoshBerke
2009-03-05 16:24:42