SQL SERVER – Create Database Role with simple example using tsql Script

Create Database Role

 
Many time SQL DBA gets request to assign permisison to database user and create database role for application developers. SQL server comes with inbuilt 
database role when you create new database by default database role gets created example db_datareader which gives you permission to view table data. 
 
In SQL Server, you can create user defined database role. User defined database role are very useful to assign permisison as wellas capture permission 
related information later whenever mamangement demand. Using these database role you can limit permission to users. Also if you are creating
database role that means same role can be used to assign permisison for multiple user.  

Example:

 Application Developer needs access to view and execute stored procedure in database. Here you can create database role and assign execute permission to that role, 

now mapp the developer id to database role.
 

Script:

 USE Testdb
GO
CREATE ROLE db_executor
GO
GRANT EXECUTE to db_executor;
GRANT VIEW DEFINITION to db_executor;
GO
 
— Lets create login for application developer and assign permission using database role.
 
USE master 
GO 
CREATE LOGIN app_Developer WITH PASSWORD=N’Developer’ 
GO 
USE Testdb 
GO 
CREATE USER app_Developer FOR LOGIN app_Developer 
GO 
EXEC sp_addrolemember ‘db_executor’, ‘app_Developer’ 
GO
 
— If you want schema level permission then create a user defined database role and GRANT that role the VIEW DEFINITION permission 
— on the dbo Schema in the Testdb Database.
 
USE Testdb
GO
CREATE ROLE dbo_viewer 
GO 
GRANT VIEW DEFINITION ON SCHEMA ::dbo TO dbo_viewer 
GO
 
Then add the app_developer user in the database to the dbo_viewer role in the database:
 
USE [Test] 
GO 
EXEC sp_addrolemember ‘dbo_viewer’,’app_developer’ 
GO
 
 
Done. Now developer can view defintion as well as execute stored procedure under testdb database. 

Leave a Reply

  

  

  


− 1 = 1