Creating new user in SQL Server (SQL Server Management Studio) is easy. Simply click on the security tab and you get an option of creating a new user but creating roles is not that easy. AT least you cannot create role using the GUI option, At least I could not. Here I will explain how to create roles and how to use them.
Creating Roles in SQL Server
Creating Roles in SQL Server is a two step process, if you want to include assinging user, then it is three step
- Create Roles in itself (step 1)
- Grand permission to the role (step 2)
- Assign/attached user to the role (step 3)
Role in itself is nothing but simply a name. For example I create a role called Super-administrator This role cannot do anything because it is just a name. It has not been given any access to the database. To make it a useful Role, we have to give it certain permission using SQL Grant Statements. I could not create them using a GUI options in SQL Server Management Studio 2005/2008.
Create a role (step 1)
USE TestDB — create role in this database
CREATE ROLE Guest_Access AUTHORIZATION [dbo]
Note that this will create role in TestDB database not in the master database!
Grant Permissions to the Role (step 2)
Next step is to assign permission to the role. We want this role the permission to select, update, delete and execute (store procedures) permission so we issue the following command.
GRANT EXECUTE, SELECT, UPDATE, DELETE
ON SCHEMA ::dbo
Execute – Can execute Store Procedure and functions
Select – Can fetch data from the database
Update – Can update data
Delete – Can delete a record
You may omit either of the four to give different permissions.
Attach User to the role (step 3)
The last step is to attached the role to a user of the database. User an be added only in the master database > Security tab not in TestDB > Security tab. The best way is to create a user in the master database, give him access to all the database that you want him to have access too by Right clicking on the user and going to User Mapping. Here select the databases he should have access too. Once this step is complete assign him a role in that database
EXEC sp_addrolemember N’Guest_Access’, N’Guest_User’
Note that we have used sp_addrolemember a stored procedure supplied by Microsoft to assign the user Guest_User to the role Guest_Access.
Important point to not here that you must create a role, give him permission. Make sure the user already exists in the database, if not you have to create the user using GUI and then assign user the role.