List sql database roles

13 May,2013 by Jack Vamvas

The sys.database_principals view returns the sql database roles. The view returns all the  principles , but using the type = ‘R’ predicate limits the recordset to database roles

I find this query useful when scheduling database moves from different environments. Sometimes  database roles have to be created to allow elevated privileges, as part of a condition I’ll check to for the existence of a database role.

To view members attached to database roles read : SQL Database roles and members

 

--list all database roles 
SELECT  name,type,type_desc,is_fixed_role
FROM sys.database_principals WHERE type = 'R'

--check for a database role , before creating a new database role
use myDB
go
if not exists(SELECT  * 
FROM sys.database_principals WHERE name = 'special_execute')
BEGIN
CREATE ROLE special_execute
GRANT EXECUTE ON sp_a_smape TO [database_user]
END


 

Read More

SQL Database roles and members

Powershell sql server security audit - SQL Server DBA

How to create a SQL Server Security Audit - SQL Server DBA

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on List sql database roles


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer