SQL Server-Level Roles And Permissions

Gagan Sharma
9y
3.9k
0
2

Article

SQL Server 2014 has the functionality to provide various kinds of permission levels that are necessary for the database security. Server for public will have access on server databases that are authenticated by the Main Security server Administrator.

Administrator has the responsibility to manage large number of database users in a way to assign more specific permissions to users/developers while creating a SQL Server Login for them. Server Roles cannot be deleted or modify.

SQl Server 2014 Permissions Roles as follows: Database roles are seen below under any database that is created with permissions on it.

Server Lever Permission Types: The following is the description of Permissions available with server roles as following given:

Image source:

Server-Level Roles: The following are the server level roles:

Image source.

You can see your Server Permissions by typing the following command:

Query: SELECT * FROM Sys.fn_Builtin_Permissions ('SERVER') ORDER BY Permission_Name;

Creating Database Permission through Wizard: In SSMS Object Explorer Permissions can be assigned by wizard as in the following,

When you clicked on New Database Role, A wizard will get open: Fill Role Name, then select Owner by clicking on '…' tab,

Now select Object Type by Browse option, select Machine Objects available in it, I selected Public, click on Ok

After that you need to select Role Members under Members of this Role: for this click on Add option, then Browse , Select Guest or Public. I selected Guest.

Now click on 2nd option Securables and select the Securables by clicking on Search option to Add objects and specify it. I selected All objects as the types... as in the following,

After clicking on OK option, a window will open, now select the Objects Types like Table, Queues, Database Roles, Schemas, Users etc. and complete selection.

Now click on Ok button.

Fill the Explicit Permission: By Ticking Grant, Deny.

Click on OK to get Success Report and a new role will be created.

GRANT is used for allowing permission on database objects changes like Update, Select, Insert etc. DENY permission denies the database from any modifications.