Create A Role and Assign Permission to the same

As a DBA we will be having many day to day tasks. One of which may be assigning roles for users to database.  This is pretty easy if you want to create just one or two users and assign or create one or two roles to the same. Now, what if there are 100 logins to be created for different databases and create multiple roles and grant access to the same.  Now we can easily achieve this by just creating a group in domain level in Active Directory for each role you want (Please contact your system Administrator if you are not enough well versed with Active Directory) and add all the users to this group and just create role for this Domain group and grant the access you want for this role. For this this is what I do. I have a T-SQL script which makes my life easy in such cases. You can find the script as mentioned below.
/* Create a login which will Domain Group
   created in Active Directory */
USE [master]
GO
/* I prefer using naming convention
   which will be easy for tracking
   Where MyDomain is your Domain and
   RoleName is the Role/Roles you want
   to be granted permissions and
   YourDatabase is your database name*/
CREATE LOGIN [MyDomain\RoleName_Securables] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
/* Create/Map user to database
   on which you want the role permissions */
USE [YourDatabase]
GO
CREATE USER [MyDomain\RoleName_Securables] FOR LOGIN [MyDomain\RoleName_Securables]
GO
/* Create Role and Role Member to Your login
   created and grant permission to same. Here for example
   i have taken VIEW DEFINITION Role, where RoleName is the
   name of the role
USE [YourDatabase]
GO
CREATE ROLE [RoleName]
GO
USE [YourDatabase]
GO
EXEC sp_addrolemember N’RoleName’, N’MyDomain\RoleName_Securables’
GO
use [YourDatabase]
GO
GRANT VIEW DEFINITION TO [RoleName]
GO
We are all set just replace the conventions used above with your actual values and execute it in a Query Browser.
Note you can also do this for individual account which may be either Domain Account or SQL Login. All you need to do is replace MyDomain\RoleName_Securables with the Domain Account or SQL Login.
Make sure you replace the Windows Group login creation script
/* Create a login which will Domain Group
   created in Active Directory */
USE [master]
GO
/* I prefer using naming convention
   which will be easy for tracking
   Where MyDomain is your Domain and
   RoleName is the Role/Roles you want
   to be granted permissions and
   YourDatabase is your database name*/
CREATE LOGIN [MyDomain\RoleName_Securables] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
With below mentioned script
/* For creating a SQL login */
USE [master]
GO
CREATE LOGIN [MySQLLogin] WITH
PASSWORD=N’StrongPasswordHere’
MUST_CHANGE,
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
GO 
Advertisements