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.
]]>