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:
|
1 2 |
CREATE LOGIN myLoginName WITH PASSWORD = 'password', SID = 0x20D2570AEC3E7B48AD1874C1D5F16492 GO |
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:
|
1 |
SELECT suser_sid('myLoginName'); |
SQL Login and Account Functions
|
1 2 3 4 5 6 |
CURRENT_USER SYSTEM_USER USER_ID SUSER_SID USER_NAME SUSER_SNAME |
SQL to identify which server logins have SysAdmin or SecurityAdmin rights
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT sl.name ,sl.denylogin ,sl.isntname ,sl.isntgroup ,sl.isntuser ,sl.sysadmin ,sl.securityadmin FROM master.dbo.syslogins sl WHERE sl.sysadmin = 1 OR sl.securityadmin = 1 |
…will return these results:
|
[imageleft|Security Script Results|{UP(SQL-Server-2012-Security-Notes)}SecurityResults.PNG] |
Views – 2089