MSSQL Roles

Add User to Existing Role

USE [mydatabase]

GO

ALTER ROLE [db_datareader] ADD MEMBER [myuser]

GO

Generate DDL

Generate DDL for existing Roles...

SET NOCOUNT ONUSE [mydatabase]DECLARE @RoleName VARCHAR(50) = 'myRole' SELECT 'CREATE ROLE ' + @RoleName + ';'+ CHAR(13)SELECT 'GRANT ' + prm.permission_name + ' ON [' + s.name + + '].[' + OBJECT_NAME(major_id) + '] TO [' + rol.name + '] ;' + CHAR(13) COLLATE Latin1_General_CI_AS  FROM sys.database_permissions prm   JOIN sys.database_principals rol ON prm.grantee_principal_id = rol.principal_id  JOIN sys.objects AS o  ON prm.major_id = o.object_id   JOIN sys.schemas AS s  ON o.schema_id = s.schema_id WHERE rol.name = @RoleName

Roles

db_owner

USE [myDatabase]

GO

SELECT  m.name      AS 'members_name',

        r.name      AS 'roles_name',

        r.type_desc AS 'roles_desc',

        m.type_desc AS 'members_desc'

   FROM sys.database_role_members rm

  INNER JOIN sys.database_principals r ON rm.role_principal_id   = r.principal_id

  INNER JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id

  WHERE r.name = 'db_owner'

  ORDER BY m.name

db_securityadmin

db_accessadmin

db_backupoperator

db_ddladmin

db_datawriter

db_datareader

db_denydatawriter

db_denydatareader

Bibliography