I am trying to create a LOGIN to SQL Server using an Active Directory Group. This group should have the least amount of privileges required to perform their required tasks. The members of this group are users that monitor and operate a lot of the administrative tasks on the database but should not be allowed to view database content. They will need to be able to view the server state, perform traces, add databases to availability groups, restore databases and view metadata though. With below script the member of the group can view the server state etc. using a member that belongs to the group but I am not able to see metadata or add databases to availability groups. If I run the same script but create a LOGIN for a user instead of a group it works much better.
What am I missing in order to get this to work for a AD group instead of a user? The error that I get when logging in with user account belonging to the group is as follows:
Error: "The server principal "MACHINENAME\USERNAME" is not able to access the database "AdventureWorks2012" under the current security context. (Microsoft SQL Server, Error: 916)"
Script:
USE [master];
IF NOT EXISTS( SELECT *
FROM sys.server_principals
WHERE name = N'BUILTIN\Users' )
BEGIN
DECLARE @CreateError INT;
CREATE LOGIN [BUILTIN\Users] FROM WINDOWS;
SET @CreateError = @@ERROR;
IF(@CreateError = 0)
BEGIN EXEC master.dbo.sp_addsrvrolemember
@loginame = 'BUILTIN\Users',
@rolename = 'OperationRole';
END;
GRANT CONNECT SQL TO [BUILTIN\Users];
USE [master];
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'BUILTIN\Users' )
CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users] WITH DEFAULT_SCHEMA = [dbo];
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'OperationRole'
AND type = 'R' )
BEGIN
CREATE ROLE [OperationRole] AUTHORIZATION [dbo];
GRANT EXECUTE ON [dbo].[CommandExecute] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[CommandExecute] TO [OperationRole];
GRANT EXECUTE ON [dbo].[DatabaseBackup] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[DatabaseBackup] TO [OperationRole];
GRANT EXECUTE ON [dbo].[DatabaseIntegrityCheck] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[DatabaseIntegrityCheck] TO [OperationRole];
GRANT EXECUTE ON [dbo].[DatabaseRestore] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[DatabaseRestore] TO [OperationRole];
GRANT EXECUTE ON [dbo].[GenerateRestoreScript] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[GenerateRestoreScript] TO [OperationRole];
GRANT EXECUTE ON [dbo].[sp_BlitzCache] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[sp_BlitzCache] TO [OperationRole];
GRANT EXECUTE ON [sys].[sp_readerrorlog] TO [OperationRole];
GRANT EXECUTE ON [dbo].[sp_WhoIsActive] TO [OperationRole];
GRANT VIEW DEFINITION ON [dbo].[sp_WhoIsActive] TO [OperationRole];
END;
IF EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'OperationRole'
AND type = 'R' ) EXEC sp_addrolemember
N'OperationRole',
N'BUILTIN\Users';
USE [msdb];
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'BUILTIN\Users' )
CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users] WITH DEFAULT_SCHEMA = [dbo];
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentUserRole'
AND type = 'R' )
BEGIN
CREATE ROLE [SQLAgentUserRole] AUTHORIZATION [dbo];
GRANT EXECUTE ON [dbo].[sp_add_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_add_jobschedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_add_jobserver] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_add_jobstep] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_add_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_addtask] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_attach_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_check_for_owned_jobs] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_check_for_owned_jobsteps] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_jobschedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_jobserver] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_jobstep] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_jobsteplog] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_delete_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_detach_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_droptask] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_enum_sqlagent_subsystems] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_get_job_alerts] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_get_jobstep_db_username] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_get_sqlagent_properties] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_category] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobactivity] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobcount] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobhistory] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobhistory_full] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobhistory_sem] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobhistory_summary] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobs_in_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobschedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobserver] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobstep] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_jobsteplog] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_operator] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_proxy] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_help_schedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_maintplan_subplans_by_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_notify_operator] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_start_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_stop_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_uniquetaskname] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_update_job] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_update_jobschedule] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_update_jobstep] TO [SQLAgentUserRole];
GRANT EXECUTE ON [dbo].[sp_update_schedule] TO [SQLAgentUserRole];
GRANT SELECT ON [dbo].[syscategories] TO [SQLAgentUserRole];
GRANT SELECT ON [dbo].[sysjobs_view] TO [SQLAgentUserRole];
GRANT SELECT ON [dbo].[sysschedules_localserver_view] TO [SQLAgentUserRole];
ALTER AUTHORIZATION ON SCHEMA ::[SQLAgentUserRole] TO [SQLAgentUserRole];
END;
IF EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentUserRole'
AND type = 'R' ) EXEC sp_addrolemember
N'SQLAgentUserRole',
N'BUILTIN\Users';
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentReaderRole'
AND type = 'R' )
BEGIN
CREATE ROLE [SQLAgentReaderRole] AUTHORIZATION [dbo];
ALTER AUTHORIZATION ON SCHEMA ::[SQLAgentReaderRole] TO [SQLAgentReaderRole];
END;
IF EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentReaderRole'
AND type = 'R' ) EXEC sp_addrolemember
N'SQLAgentReaderRole',
N'BUILTIN\Users';
IF NOT EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentOperatorRole'
AND type = 'R' )
BEGIN
CREATE ROLE [SQLAgentOperatorRole] AUTHORIZATION [dbo];
GRANT EXECUTE ON [dbo].[sp_enum_login_for_proxy] TO [SQLAgentOperatorRole];
GRANT EXECUTE ON [dbo].[sp_help_alert] TO [SQLAgentOperatorRole];
GRANT EXECUTE ON [dbo].[sp_help_notification] TO [SQLAgentOperatorRole];
GRANT EXECUTE ON [dbo].[sp_help_targetserver] TO [SQLAgentOperatorRole];
GRANT EXECUTE ON [dbo].[sp_purge_jobhistory] TO [SQLAgentOperatorRole];
GRANT SELECT ON [dbo].[sysalerts] TO [SQLAgentOperatorRole];
GRANT SELECT ON [dbo].[sysalerts_performance_counters_view] TO [SQLAgentOperatorRole];
GRANT SELECT ON [dbo].[sysnotifications] TO [SQLAgentOperatorRole];
GRANT SELECT ON [dbo].[sysoperators] TO [SQLAgentOperatorRole];
ALTER AUTHORIZATION ON SCHEMA ::[SQLAgentOperatorRole] TO [SQLAgentOperatorRole];
END;
IF EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'SQLAgentOperatorRole'
AND type = 'R' ) EXEC sp_addrolemember
N'SQLAgentOperatorRole',
N'BUILTIN\Users';
IF EXISTS( SELECT *
FROM sys.database_principals
WHERE name = N'db_backupoperator'
AND type = 'R' ) EXEC sp_addrolemember
N'db_backupoperator',
N'BUILTIN\Users';
END;
GO
ALTER SERVER ROLE dbcreator ADD MEMBER [BUILTIN\Users] ;
GO
GRANT ALTER ANY AVAILABILITY GROUP TO [BUILTIN\Users];
GO
GRANT ALTER ANY DATABASE TO [BUILTIN\Users];
GO
GRANT ALTER TRACE TO [BUILTIN\Users];
GO
GRANT CREATE ANY DATABASE TO [BUILTIN\Users];
GO
GRANT CREATE AVAILABILITY GROUP TO [BUILTIN\Users];
GO
GRANT VIEW ANY DEFINITION TO [BUILTIN\Users];
GO
GRANT VIEW SERVER STATE TO [BUILTIN\Users];
GO
I have realized that the reason why I am unable to view the metadata is because I the user does not exist in each database. So I would need to create that user for each database with no privileges in order to see the metadata. Unfortunately the following code is not working for me.
Could you help me get the below code working?
DECLARE @DB_Name varchar(100)
DECLARE @Command nvarchar(200)
DECLARE database_cursor CURSOR FOR
SELECT name
FROM MASTER.sys.sysdatabases
where dbid > 4
OPEN database_cursor
FETCH NEXT FROM database_cursor INTO @DB_Name
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @Command = 'IF NOT EXISTS( SELECT * FROM sys.database_principals
WHERE name = N''BUILTIN\Users'')
CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users] WITH DEFAULT_SCHEMA = [dbo]'
EXEC sp_executesql @Command
FETCH NEXT FROM database_cursor INTO @DB_Name
END
CLOSE database_cursor
DEALLOCATE database_cursor
Aucun commentaire:
Enregistrer un commentaire