This script is very helpful when you need to know all the permissions a user have in detail.
SELECT distinct
@@SERVERNAME as ServerName
, UserName as LoginName
, UserType as LoginType
, DatabaseUserName
, Role
, PermissionType
, PermissionState
, DatabaseName = db_name()
, ObjectName -- can be an object or the entire database
, ObjectType
, ColumnName
-- , item -- used to check consistencies in each subquery
FROM (
SELECT
UserName = princ.name,
UserType = CASE princ.type
WHEN 'S' THEN 'SQL'
WHEN 'U' THEN 'Windows'
WHEN 'G' THEN 'Windows Group'
WHEN 'R' THEN 'Database Role'
ELSE princ.type
END,
DatabaseUserName = princ.name,
Role = princ.name,
PermissionType = perm.permission_name,
PermissionState = perm.state_desc,
ObjectType = obj.type_desc,
ObjectName = coalesce(OBJECT_NAME(perm.major_id), db_name()),
ColumnName = col.name,
1 as item
FROM
sys.database_principals princ
LEFT JOIN sys.login_token ulogin on princ.sid = ulogin.sid
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id
LEFT JOIN 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
WHERE perm.permission_name <> 'CONNECT'
UNION
SELECT
UserName = memberprinc.name ,
UserType = CASE memberprinc.type
WHEN 'S' THEN 'SQL'
WHEN 'U' THEN 'Windows'
WHEN 'G' THEN 'Windows Group'
WHEN 'R' THEN 'Database Role'
else memberprinc.type
END,
DatabaseUserName = memberprinc.name,
Role = roleprinc.name,
PermissionType = perm.permission_name,
PermissionState = perm.state_desc,
ObjectType = obj.type_desc,
ObjectName = db_name(),
ColumnName = col.name,
2 as item
FROM
sys.database_role_members members
INNER JOIN sys.database_principals roleprinc ON roleprinc.principal_id = members.role_principal_id
INNER JOIN sys.database_principals memberprinc ON memberprinc.principal_id = members.member_principal_id
LEFT JOIN sys.login_token ulogin on memberprinc.sid = ulogin.sid
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = roleprinc.principal_id
LEFT JOIN 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
UNION
SELECT
UserName = roleprinc.name,
UserType =
CASE roleprinc.type
WHEN 'S' THEN 'SQL'
WHEN 'U' THEN 'Windows'
WHEN 'G' THEN 'Windows Group'
WHEN 'R' THEN 'Database Role'
ELSE roleprinc.type
END,
DatabaseUserName = roleprinc.name,
Role = roleprinc.name,
PermissionType = perm.permission_name,
PermissionState = perm.state_desc,
ObjectType = obj.type_desc,
ObjectName = coalesce(OBJECT_NAME(perm.major_id), db_name()),
ColumnName = col.name,
3 as item
FROM
sys.database_principals roleprinc
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = roleprinc.principal_id
LEFT JOIN sys.columns col on col.object_id = perm.major_id AND col.column_id = perm.minor_id
LEFT JOIN sys.objects obj ON obj.object_id = perm.major_id
UNION
SELECT
UserName = princ.name collate Latin1_General_CI_AS,
UserType = CASE princ.type
WHEN 'S' THEN 'SQL'
WHEN 'U' THEN 'Windows'
WHEN 'G' THEN 'Windows Group'
WHEN 'R' THEN 'Database Role'
ELSE princ.type
END ,
DatabaseUserName = princ.name collate Latin1_General_CI_AS,
Role = CASE
WHEN logins.sysadmin = 1 THEN 'sysadmin'
WHEN logins.securityadmin = 1 THEN 'securityadmin'
WHEN logins.serveradmin = 1 THEN 'serveradmin'
WHEN logins.setupadmin = 1 THEN 'setupadmin'
WHEN logins.processadmin = 1 THEN 'processadmin'
WHEN logins.diskadmin = 1 THEN 'diskadmin'
WHEN logins.dbcreator = 1 THEN 'dbcreator'
WHEN logins.bulkadmin = 1 THEN 'bulkadmin'
ELSE 'Public'
END,
PermissionType = perm.permission_name,
PermissionState = 'GRANT',
ObjectType = NULL,
ObjectName = princ.default_database_name,
ColumnName = NULL,
4 as item
FROM sys.server_principals princ
INNER JOIN sys.syslogins logins ON princ.sid = logins.sid
LEFT JOIN sys.database_permissions perm ON perm.grantee_principal_id = princ.principal_id
WHERE princ.type <> 'R' AND princ.name NOT LIKE '##%'
) P
where (Role <> 'Public' or ObjectName = db_name())
ORDER BY
P.DatabaseUserName,
P.ObjectName,
P.ColumnName,
P.PermissionType,
P.PermissionState,
P.ObjectType
SqlPackage is a command-line utility that you can use for exporting and importing operations in both on-premises SQL Server databases and in cloud databases. SqlPackage supports the following operations:
In this post I’m going to talk about locks on SQL Server. Locks are necessary, they are used in all operations in the database. Don’t get confused about blocking, locking and blocking are totally different.
