Skip to content

Posts from the ‘SQL Scripts’ Category

Database users migration – Migrate all permissions

Here are some of the useful scripts that will be handy when you have to migrate database users alog with all the permissions to another database. You have to run all the below scripts in source database to list out the permissions, etc and the output of each script will be a new script which you cn run on target server to grant the permissions.

1) This script will list out all the database users.

SELECT 'CREATE USER [' + name + '] for login [' + name + ']' as '--Create Users--' from sys.database_principals
 where Type = 'U' and name <> 'dbo'

2) This script will list ut all the database roles granted to users.

SELECT 'EXEC sp_AddRoleMember ''' + roles.name + ''', ''' + users.name + '''' as '--Grant Database Roles--'
 from sys.database_principals users
 inner join sys.database_role_members link
 on link.member_principal_id = users.principal_id
 inner join sys.database_principals roles
 on roles.principal_id = link.role_principal_id

3) This script will list out all the object level permissions for each database user.

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
 + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
 + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
 + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
 + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Grant Object Level Permissions--'
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.objects AS obj
 ON perm.major_id = obj.[object_id]
 INNER JOIN
 sys.database_principals AS usr
 ON perm.grantee_principal_id = usr.principal_id
 LEFT JOIN
 sys.columns AS cl
 ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY perm.permission_name ASC, perm.state_desc ASC

4) This script will list out all the database level permissions for each user.

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
 + SPACE(1) + perm.permission_name + SPACE(1)
 + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
 + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Grant Database Level Permissions--'
FROM sys.database_permissions AS perm
 INNER JOIN
 sys.database_principals AS usr
 ON perm.grantee_principal_id = usr.principal_id
WHERE perm.major_id = 0
ORDER BY perm.permission_name ASC, perm.state_desc ASC

SQL Query consuming high CPU

You can run below query to find the top 20 queries that are consuming high CPU. This query will pull data from dm_exec_query_stats DMV and will be very useful in scenarios when your SQL Server is consuming high CPU.

select top 20 * from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) order by total_worker_time desc[\sql]