December 7, 2011 at 1:08 pm
I have a login that I currently use to collect information on a SQL Server instance, including instance properties, databases and their properties, logins, etc.
A newly-added requirement is to also record the users in each database. I've come up with a few ways to do this, but none have been satisfactory:
1.) grant sysadmin to the login - I've tried hard to keep this login's privileges to a minimum, so I'd like to avoid this option
2.) create user in each database and grant select on sys.database_principals - This would be difficult to maintain with new databases being created
3.) create a certificate-signed stored proc - This would require a database on each server to hold the procedure. Not ideal, but more palatable than the first 2 options.
Does anyone have any other suggestions?
December 7, 2011 at 1:32 pm
Colleen M. Morrow (12/7/2011)
I have a login that I currently use to collect information on a SQL Server instance, including instance properties, databases and their properties, logins, etc.A newly-added requirement is to also record the users in each database. I've come up with a few ways to do this, but none have been satisfactory:
1.) grant sysadmin to the login - I've tried hard to keep this login's privileges to a minimum, so I'd like to avoid this option
2.) create user in each database and grant select on sys.database_principals - This would be difficult to maintain with new databases being created
3.) create a certificate-signed stored proc - This would require a database on each server to hold the procedure. Not ideal, but more palatable than the first 2 options.
Does anyone have any other suggestions?
1. Is this a SQL user or a domain account? While limiting rights is admirable I think you might have to use elevated rights..
2. You can add the user in model and unless you restored a database this would create the user in every newly created database..
3. I don't think this would do it either, you have cross database problems that are similar to #2.
You might look at the server role: securityadmin
http://msdn.microsoft.com/en-us/library/ms189610(v=sql.90).aspx
CEWII
December 7, 2011 at 1:38 pm
Thanks, Elliott.
This is a SQL Login. Unfortunately, even securityadmin isn't enough to view database users.
December 7, 2011 at 1:57 pm
Well shoot.. I seem to remember a similar problem when I was writing my inventory and auditing package thats on codeplex.
I elected to use a user with sysadmin rights due to needing so much deep information about individual databases I just couldn't get it any other palatable way.. I prefer to use trusted authentication whenever I can, even if that requires a proxy on the originating side..
CEWII
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply