If you don’t want to use the built-in dbo_role, a custom role with the following permissions can be created.
To do this, the new role must be created in both the BDNA and BDNA_PUBLISH databases with the following SQL:
CREATE ROLE [FlexeraRole];
GO
GRANT CREATE TABLE TO [FlexeraRole];
GRANT CREATE VIEW TO [FlexeraRole];
GRANT CREATE PROCEDURE TO [FlexeraRole];
GRANT CREATE FUNCTION TO [FlexeraRole];
GRANT CREATE SCHEMA TO [FlexeraRole];
GRANT CREATE TYPE TO [FlexeraRole];
GRANT CREATE DEFAULT TO [FlexeraRole];
GRANT CREATE RULE TO [FlexeraRole];
GO
GRANT ALTER ON SCHEMA::dbo TO [FlexeraRole];
GRANT REFERENCES ON SCHEMA::dbo TO [FlexeraRole];
GRANT CONTROL ON SCHEMA::dbo TO [FlexeraRole];
GRANT INSERT, DELETE, UPDATE, SELECT ON SCHEMA::dbo TO [FlexeraRole];
GO
GRANT EXECUTE ON SCHEMA::dbo TO [FlexeraRole];
GO
ALTER ROLE [FlexeraRole] ADD MEMBER [DOMAIN\USER]
GO
NOTE:
• The grant control on the dbo schema is needed for normalization to work.
• The last lines add a Windows domain user as a member of the role.
on Sep 18, 2024 09:24 AM - edited on Sep 18, 2024 01:08 PM by HollyM