Hello Folks,
In the previous article-post, I have discussed various parameters used in SQL Server Logins like Create, Remove, Changing, etc. So if you want to review it, check the link;
While in this article, I will basically focus on the different Server Roles that are being used while dealing with SQL Server Logins.
Server Roles:
Well, SQL Server includes only predefined and fixed server roles. These various roles grant the permission to perform certain server-related administrative tasks.
The user can belong to more than one server roles.
As you can also see the various server roles that’s being offered to the sa account:
The following are the different server roles that are being used to delegate certain server administrative tasks:
- BulkAdmin: Used to perform bulk insert operations.
- DbCreater: Used to create, alter, drop, and restore databases.
- DiskAdmin: Used to create, alter, and drop disk files.
- ProcessAdmin: Used to kill a running SQL Server Process.
- SecurityAdmin: Used to manage the logins of the Server.
- ServerAdmin: Used to configure the serverwide settings, including setting up full-text searches and shutting down the server.
- SetupAdmin: Used to configure linked servers, extended stored procedures, and the startup stored procedure.
- SysAdmin: Used to perform any activity in the SQL Server installation, regardless of any other permission setting. SysAdmin role even overrides denied permissions on an object.
NOTE: Till the previous versions, SQL Server automatically creates a user (BUILTIN\ADMINISTRATOR), that includes all the Windows users in the Windows Admins group and allows a choice of what groups or users are added during setup.
But with the arrival of SQL Server 2008 R2, it is no longer added automatically, i.e., accounts are no longer automatically granted access to SQL Server because of their membership in the Windows Administrator group. So, if you want to add the group, you must explicitly grant permission to log on to SQL Server.
It would be a lot better if the DBA’s and the Developer won’t use the sa user, rather than they should use Windows Authentication and assign them to a sysadmin role.
Assigning of Server Role:
Again this can be performed by going through either of the two ways:
- With the Management Studio
- With T-SQL Stored Procedure
With the help of Management Studio, you can assign the server role to the logins as follows:
1. Under the Security tab, click the Logins, then see this:
2. In the Logins Properties page, click on Server Roles, check the Roles you want to grant the user, and at last click the Ok button.
Now, with the help of T-SQL, a user can be assigned to a system stored procedure as follows:
EXEC sp_addsrvrolemember 'SQLServerGeeks', 'sysadmin'
Here, the Login name is SQL ServerGeeks and the server role is sysadmin.
We can also remove the Login name SQLServerGeeks from a server as follows:
EXEC sp_dropsrvrolemember 'SQLServerGeeks', 'sysadmin'
Well this was all about SQL Server Logins, and the Server Security in SQL Server, and with this we come to an end of this sequel.
Hope you got it understood well 🙂
And also comments on this!!
Regards
Piyush Bajaj
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Follow me on Twitter | Follow me on FaceBook
