Search Knowledge Base
KB #240088: Securing against the Sysadmin
SQL Server automatically allows members of the Sysadmin Role to view any and all data. However there are situations where you do not want a Sysadmin to view certain data, but you do want to enable them to maintain and administer the server.
SQL Server automatically allows members of the Sysadmin Role to view any and all data. However there are situations where you do not want a Sysadmin to view certain data, but you do want to enable them to maintain and administer the server. The fact is, that you do not really need anyone in the Sysadmin role: you can assign lesser roles to logins to allow them to perform necessary operations without giving them access to databases. For example, you need someone in the Backup Admin Role to create / restore backups, someone in the Security Admin Role to add/remove logins, etc.This is particularly valuable if you are distributing a SQL-based application and wish to prevent the customer from ad-hoc access of your database through Management Studio or Enterprise Manager.
This procedure guides you through the steps of removing Logins from the Sysadmin Role and assigning them other, lesser Roles.
Foreign Admin is the Administrator you are attempting to lock out of your application database, but who may need to perform administrative tasks such as Backup and Restore.
The only prerequisites for this technique are:
- You must use SQL Authentication Mode or Mixed Mode (SQL and Windows).
- You must assign an SA Password to the instance. In addition, the SA Password should be unknown to anyone who is not supposed to have access to the database.
Overview of Installation Process
- Install Encryptionizer.
- Install instance of SQL or SQL Express if not already present.
- Use build3(secbuild.dll) or bldcmd.exe to secure instance of SQL.
- Limit Sysadmin access using procedure outlined below.
- Copy in encrypted database, or encrypt database on-the-fly using N_ENCODEFILE, N_ENCODEFILEEX or SECTOOL.EXE.
- The “SA” login password must be changed to something unknown to the Foreign Admin.
- The Builtin\Administrators login must be “demoted” from the sysadmin role. However, it can be given any necessary permissions required to handle administrative tasks, such as backups. More on this in section Limiting Sysadmin Access.
This procedure “demotes” members of the sysadmin role so they no longer have sysadmin rights. In addition, it changes the password for SA, since it cannot be demoted.The following procedure can be performed from Enterprise Manager, or from Query Analyzer. In either case, it requires SA access. If you are using Query Analyzer, we list the procedures you will need for each step.
1. If you are currently using only Windows Authentication Mode, you must switch to Mixed or SQL Authentication Mode.
From Managerment Studio or Enterprise Manager:
- Right click on instance
- Select Properties (not Registration Properties)
- Under Authentication, select:
– SQL 2005/2008: SQL Server Authentication (which is really “mixed”)
– SQL 2000: SQL Server and Windows
1a. If you will be doing an unattended installation of SQL, make sure to specify the /SECURITYMODE and /SAPWD parameters in the installation command line. For more information search for “sql server unattended installation” in msdn.microsoft.com.
2. Change SA password to a value unknown to the Foreign Admin. If doing this programmatically you can use the system stored procedure:
3. “Demote” Builtin\Administrators from the Sysadmin server role.
If you wish, you can add the Foreign Admin to any other Server Roles needed to accomplish permitted administrative tasks. Typically these might be:
- securityadmin: to allow them to add logins to the server
- dbcreator: to allow them to restore databases
- bulkadmin: to allow them to perform bulk insert and bcc.
If you wish to do this programmatically, you can use the system stored procedures
sp_addsrvrolemember.See MSDN Article http://msdn.microsoft.com/en-us/library/ms188659.aspx for a list of Fixed Server Roles.
3a. If there are other logins in the SysAdmin role, they should also be “demoted”. If this is a new installation, it is likely that only Builtin\Administrators is in the SysAdmin Role.
To see what other logins are in the SysAdmin role use the system stored procedure
4. Revoke the Server Permissions listed below from all logins where they are granted. These Permissions may allow a Foreign Admin “back door” access to the encrypted database(s):
- CONTROL SERVER
- VIEW ANY DATABASE
- ALTER ANY DATABASE
- CREATE ANY DATABASE
See MSDN Article http://msdn.microsoft.com/en-us/library/ms191291.aspx for a list of Server Permissions.
5. Remove the Guest user from Application database (e.g., Northwind). This should always be done, whether you are encrypting a database or not, unless you have a specific reason to keep the Guest user. (Note 1)
To do this programmatically use the system stored procedure
5a. We strongly recommend dropping Guest as a server login, as well. You can do this programmatically using the system stored procedure
6. In order to permit Builtin\Administrators (or any other login) to backup the database being protected, add them to the fixed database role db_backupoperator.
We also recommend that you add them to the following fixed database roles to ensure they are restricted from accessing the data:
- db_denydatareader: to deny select
- db_denydatawriter: to deny insert, update, delete
To do this programmatically use the system stored procedures
sp_addrolemember.7. Add a new login (Windows or SQL Authentication), e.g., ‘appuser’, which will be used by the application to connect to SQL. The login password should be unknown to the Foreign Admin. Then add the new login as a user of the database. No specific database roles are required as long as the Public role has not been changed from the default. (Note 1)
To do this programmatically use the system stored procedures
Note 1: If you are distributing a SQL-based application, you can usually pre-configure your database with these steps before including it in your installation package.
Tip: We recommend encrypting the Master Database and specifying “Master Must Be Encrypted” in the SECADMIN Additional Options Screen, otherwise the Foreign Admin can replace the Master database and gain back the SysAdmin role.
Steps 3, 3a: The following routine will locate all logins that are in the sysadmin role, remove the user from the role, and then add the user to some other useful roles.
DECLARE @errnum integer, @loginname varchar(128) Create Table #members(ServerRole varchar(128), Membername varchar(128), MemberSID varbinary(128)); Insert #members EXEC sp_helpsrvrolemember 'sysadmin' DECLARE temp_cursor CURSOR FOR select Membername from #members where MemberSID != 0x01 and MemberName not like '##%'; OPEN temp_cursor FETCH NEXT FROM temp_cursor INTO @loginname WHILE (@@FETCH_STATUS != -1) BEGIN IF (@@FETCH_STATUS != -2) BEGIN SET @loginname = RTRIM(@loginname) exec @errnum = sp_dropsrvrolemember @loginname , 'sysadmin' /* optional - add to securityadmin, dbcreator, and bulkadmin roles */ exec @errnum = sp_addsrvrolemember @loginname, 'securityadmin' exec @errnum = sp_addsrvrolemember @loginname, 'dbcreator' exec @errnum = sp_addsrvrolemember @loginname, 'bulkadmin' END FETCH NEXT FROM temp_cursor INTO @loginname END CLOSE temp_cursor DEALLOCATE temp_cursor Drop Table #members
Step 4: The following procedure will revoke the permissions mentioned in Step 4 above
DECLARE @errnum integer, @loginname varchar(128), @sql varchar(1024), @cPermission varchar(128) DECLARE temp_cursor CURSOR FOR select Name, permission_name from sys.server_permissions INNER JOIN sys.server_principals ON sys.server_permissions.grantee_principal_id = sys.server_principals.principal_id WHERE ([permission_name]='CONTROL SERVER' OR [permission_name]='VIEW ANY DATABASE' OR [permission_name]='ALTER ANY DATABASE' OR [permission_name]='CREATE ANY DATABASE') AND [state] != 'D' AND [state] != 'R' AND sys.server_principals.type != 'C' OPEN temp_cursor FETCH NEXT FROM temp_cursor INTO @loginname, @cPermission WHILE (@@FETCH_STATUS != -1) BEGIN IF (@@FETCH_STATUS != -2) BEGIN Set @loginname = RTRIM(@loginname) set @sql = 'Use master; REVOKE '+@cPermission+' TO ['+@loginname+'] CASCADE;' execute(@sql) if @errnum <> 0 break END FETCH NEXT FROM temp_cursor INTO @loginname, @cPermission END CLOSE temp_cursor DEALLOCATE temp_cursor