Category Archives: SQL Server Security – Roles and Understanding

Working and Understanding how access will be granted to USER


Scenario:
I have been tasked with auditing security on  SQL Server. I understand that logins allow you to connect to SQL Server, but I’m not quite understanding how to determine whether a login has access to a database or not. For instance, I know that all logins can access the master database, but when I look at a login in SQL Server Management Studio, I don’t see a checkbox beside the master db for that login. How can I determine what databases a login has access to?

Process & Solution:
You’re right, that if you look in SQL Server Management Studio you won’t see a login’s access to the master database unless the DBA has done something explicitly. An example is shown in Figure 1.

Figure 1:

Login with no explicit mapping to the master database

But we do know that all logins can access the master database. So let’s talk about how a login can connect to a given database. There are five possible ways a login can have permission to connect to a given database:

  1. Explicit access is granted.
  2. The login is a member of the sysadmin fixed server role.
  3. The login has CONTROL SERVER permissions (SQL Server 2005/2008 only).
  4. The login is the owner of the database.
  5. The guest user is enabled on the database.

 


 

Explicit Access (Login Mapped to Database User):

The first way is if a login is given explicit access to a database. For instance, in SQL Server 2000, if I had a user MyTestUser, I would grant access like so from within the database:

EXEC sp_grantdbaccess 'MyTestUser';
GO
 

In SQL Server 2005 and 2008 there are new T-SQL commands to create logins and users. So I would use the following command to do the same thing:

CREATE USER MyTestUser FOR LOGIN MyTestUser;
GO
 

A login granted access in this manner should appear in the sysusers table (SQL Server 2000) or the sys.database_principals catalog view (SQL Server 2005/2008).

For instance, here’s how I would match up users in a given database to their corresponding logins (SQL Server 2000):

SELECT sl.name AS 'Login', su.name AS 'User' 
FROM master..syslogins sl
  JOIN sysusers su
    ON sl.sid = su.sid
ORDER BY sl.name, su.name;
 

And here’s how we’d do it in SQL Server 2005/2008:

SELECT sp.name AS 'Login', dp.name AS 'User'
FROM sys.database_principals dp
  JOIN sys.server_principals sp
    ON dp.sid = sp.sid
ORDER BY sp.name, dp.name;

If you see a login match up to a user in this manner, then the login has access to the database.


 

Implicit Access (Member of Sysadmin Fixed Server Role):

All members of the sysadmin fixed server role map to the dbo user of every database. Therefore, if a login is a member of this role, it automatically has access to every database.

Here is the query for SQL 2000 to see members of the sysadmin fixed server role.

EXEC sp_helpsrvrolemember 'sysadmin'

 

Here is the query for SQL 2005/2008 to see members of the sysadmin fixed server role.

SELECT sp.name
FROM sys.server_role_members srm
INNER JOIN sys.server_principals sp
     ON srm.member_principal_id = sp.principal_id
WHERE srm.role_principal_id = (
     SELECT principal_id
     FROM sys.server_principals
     WHERE [Name] = 'sysadmin')


 

Implicit Access (CONTROL SERVER permission – SQL Server 2005/2008):

 

The CONTROL SERVER permission gives equivalent rights as a member of the sysadmin role with a few exceptions, which aren’t of importance here. Therefore, if a login doesn’t map explicitly to a user in a database, but that login has CONTROL SERVER permissions, that login can still access the database. You can see who has CONTROL SERVER permissions by the following query:

SELECT sp.name 'Login' 
FROM sys.server_principals sp
   JOIN sys.server_permissions perms
     ON sp.principal_id = perms.grantee_principal_id
WHERE perms.type = 'CL'     
  AND perms.state = 'G';


 

Implicit Access (Database Owner):

 

The database owner automatically maps into the database as the dbo user. The query given under explicit access should reveal the owner by just looking at the dbo user. However, another way is to query the sysdatabases table (SQL Server 2000) or sys.databases catalog view (SQL Server 2005/2008). Here’s the SQL Server 2000 query that reveals all the owners of all the databases on the server:

SELECT db.name AS 'Database', sl.name AS 'Owner' 
FROM sysdatabases db
  INNER JOIN syslogins sl
    ON db.sid = sl.sid
ORDER BY db.name;

And here’s how to do the same thing in SQL Server 2005/2008:

 

SELECT db.name AS 'Database', sp.name AS 'Owner'
FROM sys.databases db
  INNER JOIN sys.server_principals sp
    ON db.owner_sid = sp.sid
ORDER BY db.name;


 

Implicit Access (Guest User Is Enabled):

 

The final way a login can get access to a database is if the guest user is enabled for that database. If a login cannot map in any other way, it’ll use guest if that’s available. That’s actually how logins can access the master database. The guest user is enabled. With respect to user databases, the guest user should only be enabled in special cases. The default is for it to be disabled. However, there are two system databases which the guest user must always remain enabled. They are:

  • master
  • tempdb

 

And that explains why logins always have access to master, even when explicit rights aren’t visible. To see if the guest user is enabled we can query sysusers (SQL Server 2000) or sys.database_permissions (SQL Server 2005/2008). Here’s how to do it in SQL Server 2000:

SELECT su.name, CASE su.hasdbaccess WHEN 1 THEN 'Yes' ELSE 'No' END AS 'Enabled'
FROM sysusers su
WHERE su.name = 'guest';

In SQL Server 2005/2008 we have to look for the existence of the CONNECT permission at the database level for the guest user. If it exists, the guest user is enabled. If it doesn’t, then the guest user is not.

SELECT dp.name, CASE perms.class WHEN 0 THEN 'Yes' ELSE 'No' END AS 'Enabled'
FROM sys.database_principals dp
  LEFT JOIN (SELECT grantee_principal_id, class FROM sys.database_permissions 
              WHERE class = 0 AND type = 'CO' AND state = 'G') AS perms
    ON dp.principal_id = perms.grantee_principal_id
WHERE dp.name = 'guest';
 

SQL Server Security – Understanding Roles


Fixed Database Roles:

 

Fixed database roles are defined at the database level and exist in
each database.

You cannot add, delete or modify fixed database roles. You can only
add users as a member of a fixed database roles.

There are nine fixed database roles:

db_owner

db_accessadmin

db_datareader

db_datawriter

db_ddladmin

db_securityadmin

db_backupoperator

db_denydatareader

db_denydatawriter

The members of db_owner database role can perform any activity in the database.

The members of db_accessadmin database role can add or remove Windows NT groups, users or SQL Server users in the database.

The members of db_datareader database role can see any data from all user tables in the database.

The members of db_datawriter database role can add, change, or delete data from all user tables in the database.

The members of db_ddladmin database role can make any data definition language commands in the database.

The members of db_securityadmin database role can manage statement and object permissions in the database.

The members of db_backupoperator database role can back up the database.

The members of db_denydatareader database role can deny permission to select data in the database.

The members of db_denydatawriter database role can deny permission to change data in the database.

To add a security account as a member of an existing SQL Server database role in the current database, you can use sp_addrolemember system stored procedure.

This is the syntax:

sp_addrolemember [@rolename =] 'role',
    [@membername =] 'security_account'

where @rolename – is the name of the database role.

@membername – is the name of the security account.

Any member of a fixed database role can add other users to this role.

_____________________________________________________________________________________

The benefits of using roles

 
Roles are a part of the tiered security model:

  • Login security—Connecting to the server
  • Database security—Getting access to the database
  • Database objects—Getting access to individual database objects and data

First, the user must log in to the server by entering a password. Once connected to the server, access to the stored databases is determined by user accounts. After gaining access to an actual database, the user is restricted to the data he or she can view and modify.

The main benefit of roles is efficient management. Imagine a group of 1,000 users suddenly needing to view or modify new data. Using Windows security, you simply select an existing Windows group and assign it to a SQL Server role—instead of modifying 1,000 user accounts.

To clarify, Windows groups consist of users with access to the Windows network, but SQL Server roles belong strictly to SQL Server. You’re simply granting permissions to SQL Server data and objects to valid Windows users.
Role types
Server roles are maintained by the database administrator (DBA) and apply to the entire server, not an individual database file. The public role sets the basic default permissions for all users. Every user that’s added to SQL Server is automatically assigned to the public role—you don’t need to do anything. Database roles are applied to an individual database.
Predefined database roles

 
You may need to create your own, but you have access to several predefined database roles:

  • db_owner: Members have full access.
  • db_accessadmin: Members can manage Windows groups and SQL Server logins.
  • db_datareader: Members can read all data.
  • db_datawriter: Members can add, delete, or modify data in the tables.
  • db_ddladmin: Members can run dynamic-link library (DLL) statements.
  • db_securityadmin: Members can modify role membership and manage permissions.
  • db_bckupoperator: Members can back up the database.
  • db_denydatareader: Members can’t view data within the database.
  • db_denydatawriter: Members can’t change or delete data in tables or views.

 
Fixed SQL Server roles:

 
The fixed server roles are applied serverwide, and there are several predefined server roles:

  • SysAdmin: Any member can perform any action on the server.
  • ServerAdmin: Any member can set configuration options on the server.
  • SetupAdmin: Any member can manage linked servers and SQL Server startup options and tasks.
  • Security Admin: Any member can manage server security.
  • ProcessAdmin: Any member can kill processes running on SQL Server.
  • DbCreator: Any member can create, alter, drop, and restore databases.
  • DiskAdmin: Any member can manage SQL Server disk files.
  • BulkAdmin: Any member can run the bulk insert command.

 

******************************************************************************************************

%d bloggers like this: