problem new dbrole with DDL Trigger

Question

Post reply

problem new dbrole with DDL Trigger

tony28

SSCertifiable

Points: 7112

July 29, 2022 at 5:50 am

#4069798

Hello,

I create new db role only for some specific schema. This is normally working, but If I enabled our DDL trigger for checking some changes, then is error

Msg 3616, Level 16, State 2, Line 23

An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.

Does anybody have idea how to add permission to this trigger?

If I have dbowner of whole database, then is working without problem. But login can do somethingwith all schemas.

I tried GRANT CONTROL SERVER TO [Login_inRole]; but then has also access to another db and etc.

Thanks

here is example

USE [DatabaseName]

--create database role , for example owner of schema
CREATE ROLE [db_ownerSchemaName]
GO

--when is needed select to whole database
GRANT SELECT TO [db_ownerSchemaName]
GO

-- grant create procedure and table , this should be checked if really possible create only in this schema 
GRANT CREATE PROCEDURE TO [db_ownerSchemaName]

GRANT CREATE TABLE TO [db_ownerSchemaName]

GRANT CREATE VIEW TO [db_ownerSchemaName]

GRANT CREATE FUNCTION TO [db_ownerSchemaName]


-- grant ALTER, CONTROL, DELETE, EXECUTE, INSERT, SELECT, UPDATE, VIEV DEFINITION To schema
GRANT ALTER ON SCHEMA::[schemaName] TO [db_ownerSchemaName]
GO
GRANT CONTROL ON SCHEMA::[schemaName] TO [db_ownerSchemaName]
GO
GRANT DELETE ON SCHEMA::[schemaName] TO [db_ownerSchemaName]
GO
GRANT EXECUTE ON SCHEMA::[schemaName] TO [db_ownerSchemaName]
GO
GRANT INSERT ON SCHEMA::[schemaName] TO [db_ownerSchemaName]
GO
GRANT SELECT ON SCHEMA::[schemaName] TO [db_ownerSchemaName]
GO
GRANT UPDATE ON SCHEMA::[schemaName] TO [db_ownerSchemaName]
GO
GRANT VIEW DEFINITION ON SCHEMA::[schemaName] TO [db_ownerSchemaName]
GO

--grant select to table sql_expression_dependencies, because app needed
GRANT SELECT ON sys.sql_expression_dependencies TO [db_ownerSchemaName];

--create Login with password
USE [master]
GO
CREATE LOGIN [LoginInRole] WITH PASSWORD=N'PWD', DEFAULT_DATABASE=[DatabaseName], DEFAULT_LANGUAGE=[English], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

--create user in DB and add to new role 
USE [DatabaseName]
GO
CREATE USER [LoginInRole] FOR LOGIN [LoginInRole]
GO
ALTER ROLE [db_ownerSchemaName] ADD MEMBER [LoginInRole]
GO

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80325 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.