January 23, 2017 at 3:17 pm
Hello,
We are migrating to SQL 2014 and they have chosen to take away our production support team's sysadmin rights. We need to be able to SEE all the logins from the server level and the users from the database level. But NOT be able to add or change. Is this permission possible? We would like a user-defined server role that will allow this. We are in an active directory GROUP. Right now all we can only see OUR group and the deprecated sa account.
January 24, 2017 at 6:50 am
shelleybobelly - Monday, January 23, 2017 3:17 PMHello,We are migrating to SQL 2014 and they have chosen to take away our production support team's sysadmin rights. We need to be able to SEE all the logins from the server level and the users from the database level. But NOT be able to add or change. Is this permission possible? We would like a user-defined server role that will allow this. We are in an active directory GROUP. Right now all we can only see OUR group and the deprecated sa account.
you could grant the following
grant view any definition to login
grant view any database to login
However, questionable whether this is actually required
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 24, 2017 at 8:23 am
I'd write a job to put this info into table you have access to. You can update it daily if needed. Perhaps you can build a proc WITH EXECUTE AS. I'll try to test that.
January 24, 2017 at 8:44 am
I created a proc in a db, granted rights to a role, added a normal (non sysadmin/non secadmin) to the role, and this seemed to work. You'd have to modify to get user values from all dbs, but not that hard. I might make a proc or function to get users from a db, then loop that if needed.CREATE PROCEDURE GetLogins_NonSA
WITH EXECUTE AS OWNER
/*
Description:
Changes:
Date Who Notes
---------- --- ---------------------------------------------------
1/24/2017 PLATO\Steve Initial proc to get server logins
*/
AS
BEGIN
SELECT name
, principal_id
, sid
, type
, type_desc
, is_disabled
, default_database_name
FROM master.sys.server_principals
RETURN
END
GO
GRANT EXECUTE ON GetLogins_NonSA TO MigrationRole
go
ALTER ROLE MigrationRole ADD MEMBER JoeDBA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply