SQL Server Roles

SQL Server-level Roles
SysAdmin: Allows users to perform any activity on the server.
ServerAdmin: Permits users to manage configuration options on the server and shut down SQL Server.
SecurityAdmin: Gives users the ability to grant, revoke, and deny server- and database-level permissions. They can also manage users and passwords, as well as reset passwords of other users.
ProcessAdmin: Allows users to end processes running on an instance.
SetupAdmin: Creates linked servers.
BulkAdmin: Performs bulk-insert statements.
DiskAdmin: Manages the disk files.
DBCreator: Creates, alters, or drops any databases.
Public: The default role that server logins belong to.

Creating New Logins with specific SIDs
This query will create a new SQL login with a specific SID for replication purposes:

You can query the original database (master) on the original server to get the proper SID for the new login you are trying to create:


SQL Login and Account Functions

SQL to identify which server logins have SysAdmin or SecurityAdmin rights

…will return these results:

[imageleft|Security Script Results|{UP(SQL-Server-2012-Security-Notes)}SecurityResults.PNG]

Views – 571

Leave a Reply