How to verify which permissions are assigned at the database level (4218482)

How to verify which permissions are assigned at the database level

Error message "Failed to login to SQL Server"

Incorrect permissions at the master, LiteSpeedLocal or LiteSpeedCentral database level.

This script is run in Query Analyzer and displays the permissions assigned at the database level, using the sys.database_permissions catalog view along with sys.database_principals to tie to database users and roles:

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];


WORKAROUND:
Grant "ALTER" rights to user "DBUser1" using the following:

GRANT ALTER TO DBUser1

See Books Online for more info on:
GRANT - gives a user permission to perform certain tasks on database objects
DENY - denies any access to a user to perform certain tasks on database objects
REVOKE - removes a grant or deny permission from a user on certain database objects

To verify permissions at the server level run the following query. It uses the sys.server_permissions catalog view joined against the sys.server_principals catalog view to display all the server-level permissions on the SQL Server logins, Windows user logins, and Windows group logins:


SELECT
[srvprin].[name] [server_principal],
[srvprin].[type_desc] [principal_type],
[srvperm].[permission_name],
[srvperm].[state_desc]
FROM [sys].[server_permissions] srvperm
INNER JOIN [sys].[server_principals] srvprin
ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]
WHERE [srvprin].[type] IN ('S', 'U', 'G')
ORDER BY [server_principal], [permission_name];