I have given the following SQL to database user who with db_SecurityAdmin & db_AccessAdmin database roles. He doesn't see any more than his data when he runs it. I am an sa on the database and see all of the data. What security does he need in order to pull all data as a non-sa or is it possible for a user other than sa to see it all?
The other idea: If this SQL was placed in a stored procedure - would a non-sa be able to pull all of the data from it? Is there a way for them to execute the proc as sa?
SELECT sys.sql_logins.name as Login_name, sys.database_principals.name as Principal_name , sys.database_principals.type_desc , database_principals1 .name AS role_name
FROM sys.database_principals
INNER JOIN
sys.sql_logins on sys.database_principals.sid = sys.sql_logins.sid
INNER JOIN
sys.database_role_members ON sys.database_principals.principal_id = sys.database_role_members.member_principal_id
INNER JOIN
sys.database_principals AS database_principals1 ON sys.database_role_members .role_principal_id = database_principals1.principal_id
Thanks for your help!
You have this user accessing restricted tables.
You could try adding the user to the securityadmin role. If that doesn't work for you, you could create the stored procedure with EXECUTE AS permissions, and then GRANT the user permission to EXECUTE the stored procedure
|||These are system catalogs - views not tables.
This is what Online books suggested for use as the system tables could change in future releases.
If this is not the correct source for this information, where should it be retrieved from?
|||And these 'views' are accessing restricted system tables.|||If I place the SQL in a stored procedure,
what do I need to add to ensure that the user can execute it as SA.
I have tried the EXECUTE as 'sa' statement and I must be missing something because it doesn't work.
THANKS!
|||You don't have to do anything.
Anyone placed in the [sysadmin] Role (sa), can do anything in the server, including, executing any stored procedures. The [sysadmin] Role totally controls the server and all databases on the server.
You might wish to read up on 'Roles' in Books Online.
|||Even the "public" role as select permission on the catalog views. In 2005, though, this isn't enough, and accounts must be granted "view definition" permissions. For example, this statement grants permission to the "public" role to see all system metadata:
use master; grant view any definition to public
See this for more info:
http://msdn2.microsoft.com/en-us/library/ms175808.aspx
Ron Rice
