Analyzing SQL Server Permissions

One of the most important responsibilities of a SQL Server Database Administrator is to ensure that the managed SQL Servers instances are completely secure. Securing SQL Server is not as simple as ensuring that the unused features of SQL Server are not enabled unless required. The last article titled Surface Area Configuration Using Policy Based Management discussed the different features that are disabled by default and and how to enable them in SQL Server 2008 if required. Database Administrators should also periodically analyse SQL Server permissions to ensure there are no logins and users with unwanted SQL Server and Database access respectively. If there are such logins or users, then they should be removed. This article will show how database administrators can analysis SQL Server permissions on both SQL Server 2005 and SQL Server 2008 systems.

Different Authentication Methods Supported in SQL Server
Windows and SQL Server are the two different authentication modes supported in SQL Server. Windows authentication mode is considered to be more secure when compared to SQL Server Authentication. The reason is in Windows authentication mode the SQL Server validates user name and password using the Windows principal token in the operating system, the users’ identity is verified by the Windows operating system. As a result when a user connection is established to SQL Server using Windows authentication mode it is called a trusted connection. However, in the case of SQL Server Authentication both the user name and password is created using SQL Server and intern it is stored in SQL Server. The disadvantage of using SQL Server authentication is that whenever a user tries to establish a connection they need to provide both the user name and password.  Moreover, in the case of SQL Server Authentication the user validation has to be performed by the SQL Server.

Identify Logins Having Permissions to SQL Server 2005 & Later Versions
In SQL Server 2005 and later versions, information related to both the SQL Server and Windows accounts, that have permissions to connect to a particular instance of SQL Server, is stored in the  sys.server_principals system level view. Execute the TSQL query below to find the list of all the users who have access to connect to a SQL Server instance.

Use master
GO
SELECT PRINCIPAL_ID AS [Principal ID],
 NAME AS [User],
 TYPE_DESC AS [Type Description],
 IS_DISABLED AS [Status]
FROM sys.server_principals
GO



Add a new SQL Server Login named UserA by executing the TSQL code below.

Use master
GO
CREATE LOGIN UserA
WITH Password = ‘UserA’, CHECK_POLICY = OFF
GO

Assign UserA the BULKADMIN SQL Server Role. Do this by executing the TSQL code below.

Use master
GO
EXECUTE sp_addsrvrolemember
@loginame = ‘UserA’,
@rolename = ‘bulkadmin’
GO

Verify whether the newly added SQL Server Login is successfully added. This can be done by executing the TSQL code below.

Use master
GO
SELECT PRINCIPAL_ID AS [Principal ID],
 NAME AS [User],
 TYPE_DESC AS [Type Description],
 IS_DISABLED AS [Status]
FROM sys.server_principals WHERE name =’UserA’
GO

Starting with SQL Server 2005, Microsoft commenced using the term “Server Principal” for LOGINS and “Database Principal” for database USERS. The Server Principal is a LOGIN that has permissions to connect to a particular instance of SQL Server. The ability to logon to a SQL Server system it does not grant access to any of the databases. In order to access a database the SQL Server Login needs to be mapped as a Database User (Database Principal) within the user database. The next section will describe more about Database Users/Database Principals.

Identify Users Having Permissions to Databases in SQL Server 2005 & Later Versions
Now that access permissions have been granted to access SQL Server, the next step is to create a database level user for UserA in the AdventureWorks database. This can be done by executing the TSQL code below.

USE [AdventureWorks]
GO
CREATE USER [UserA] FOR LOGIN [UserA]
GO
USE [AdventureWorks]
GO
ALTER USER [UserA] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [AdventureWorks]
GO
EXEC sp_addrolemember N’db_datareader’, N’UserA’
GO

The above script creates a database user named UserA within the AdventureWorks database, and also provides the user UserA with access to the dbo schema. Additionally it adds UserA as a member of the db_datareader database level role.

To discover user related information for any of the user databases, then query the sys.database_principals system view. Executing the TSQL code below will identify the list of users available within the AdventureWorks database.

USE [AdventureWorks]
GO
SELECT
 SDP.PRINCIPAL_ID AS [Principal ID],
 SDP.NAME AS UserName,
 SDP.TYPE_DESC AS UserType, 
 SSP.NAME AS LoginName,
 SSP.TYPE_DESC AS LoginType
FROM sys.database_principals SDP
INNER JOIN sys.server_principals SSP
ON SDP.PRINCIPAL_ID = SSP.PRINCIPAL_ID
GO



Results will vary when the same query is being run against different user databases. This is because different users will have different levels of access in different databases.

Server Roles
To identify the server level role each login has on a particular instance of SQL Server is the next step. This can be done by executing the TSQL code below.

Use master
GO
SELECT
 SSP.name AS [Login Name],
 SSP.type_desc AS [Login Type],
 UPPER(SSPS.name) AS [Server Role]
FROM sys.server_principals SSP
INNER JOIN sys.server_role_members SSRM
ON SSP.principal_id=SSRM.member_principal_id
INNER JOIN sys.server_principals SSPS
ON SSRM.role_principal_id = SSPS.principal_id
GO



Note in the above snippet that UserA is assigned the BULKADMIN SQL Server Role.

Database Role
Now the SQL Server Level Role has been identified, next is identifying the database level role each user has on a particular database. This is done by executing the TSQL code below.

USE [AdventureWorks]
GO
SELECT
 SDP.name AS [User Name],
 SDP.type_desc AS [User Type],
 UPPER(SDPS.name) AS [Database Role]
FROM sys.database_principals SDP
INNER JOIN sys.database_role_members SDRM
ON SDP.principal_id=SDRM.member_principal_id
INNER JOIN sys.database_principals SDPS
ON SDRM.role_principal_id = SDPS.principal_id
GO



Note in the above snippet that UserA is assigned the DB_DATAREADER database role in AdventureWorks database.

Server Level Permissions
To learn more about server level permissions for a login, access the sys.server_permissions system view.

Use master
GO
SELECT * FROM sys.server_permissions
GO

Database Level Permissions
To learn more about database level permissions for a user, access the sys.database_permissions system view.

Use AdventureWorks
GO
SELECT * FROM sys.database_permissions
GO

Conclusion
This article has described how database administrators can leverage the inbuilt system views to analyse SQL Server Permissions in SQL Server 2005 and later versions. It is very important for a database administrator to periodically analyse logins and user permissions on a SQL Server instance to ensure that there are no logins and users who are have unnecessary access.



Related Articles :

  • No Related Articles Found

3 Responses to “Analyzing SQL Server Permissions”

  1. Hi,

    There is an error in the script that tries to match server logins with their corresponding database user.

    As it was shown above, the UserA user was added to the bulkadmin server role. However, since the join between the sys.database_principal and sys.server_principals table was done on the principal_id, it shows that the UserA was added to the serveradmin role.

    In order to correct this, instead of joining the tables by the principal_id, the sid has to be used.

  2. The database_permissions table shows all permissions that have been granted to the user explicitly but not those rights derived from being a member any of the built in roles.

  3. –SQL Server Permissions

    SELECT [UserName] = ulogin.[name],
    [UserType] = CASE princ.[type]
    WHEN ‘S’ THEN ‘SQL User’
    WHEN ‘U’ THEN ‘Windows User’
    WHEN ‘G’ THEN ‘Windows Group’
    END,
    [DatabaseUserName] = princ.[name],
    [Role] = NULL,
    [PermissionState] = perm.[state_desc],
    [PermissionType] = perm.[permission_name],
    [ObjectType] = CASE perm.[class]
    WHEN 1 THEN obj.type_desc — Schema-contained objects
    ELSE perm.[class_desc] — Higher-level objects
    END,
    [ObjectName] = CASE perm.[class]
    WHEN 1 THEN OBJECT_NAME(perm.major_id) — General objects
    WHEN 3 THEN schem.[name] — Schemas
    WHEN 4 THEN imp.[name] — Impersonations
    END,
    [ColumnName] = col.[name]
    FROM –database user
    sys.database_principals princ
    LEFT JOIN –Login accounts
    sys.server_principals ulogin
    ON princ.[sid] = ulogin.[sid]
    LEFT JOIN –Permissions
    sys.database_permissions perm
    ON perm.[grantee_principal_id] = princ.[principal_id]
    LEFT JOIN –Table columns
    sys.columns col
    ON col.[object_id] = perm.major_id
    AND col.[column_id] = perm.[minor_id]
    LEFT JOIN sys.objects obj
    ON perm.[major_id] = obj.[object_id]
    LEFT JOIN sys.schemas schem
    ON schem.[schema_id] = perm.[major_id]
    LEFT JOIN sys.database_principals imp
    ON imp.[principal_id] = perm.[major_id]
    WHERE princ.[type] IN (‘S’, ‘U’, ‘G’)
    AND — No need for these system accounts
    princ.[name] NOT IN (‘sys’, ‘INFORMATION_SCHEMA’)
    ORDER BY
    ulogin.[name],
    [UserType],
    [DatabaseUserName],
    [Role],
    [PermissionState],
    [PermissionType],
    [ObjectType],
    [ObjectName],
    [ColumnName]

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |