Posts Tagged 'Permissions'

Scripting Role and User Permissions

I recently had to copy a large number of databases to a development environment. There was several hundred gigabytes of files, so I went with stop sql services, copy, restart services and attach new databases route.

Before copying the files, I migrated the users. After attaching the database files to the new instance, I ran the orphaned user scripts. Everything seemed to have swimmingly.

A couple of hours later, a developer called me up and said that they were running into permission errors executing stored procedures. There were a lot of scripts out there for user permissions, but none of them seemed to fit my specific needs, especially since this was SQL 2000. So, on that note, here is my addition to that myriad of scripts for SQL Server permissions.

/* SQL 2005/2008 */
DECLARE
 @Login varchar(50),
 @Role varchar(50);

SET @Login = NULL;
SET @Role = NULL;

SELECT
 perm.state_desc
 + SPACE(1)
 + perm.permission_name
 + ' on ['
 + s.name
 + '].['
 + o.name
 + '] to ['
 + prin.name
 + '];'
 + CHAR(10)
 + 'GO'
 + CHAR(10)
 COLLATE LATIN1_General_CI_AS
FROM
 sys.database_permissions perm
INNER JOIN
 sys.objects o ON
 perm.major_id = o.OBJECT_ID
INNER JOIN
 sys.schemas s ON
 o.SCHEMA_ID = s.SCHEMA_ID
INNER JOIN sys.database_principals prin ON
 perm.grantee_principal_id = prin.principal_id
WHERE
 ((prin.type = 'U' AND prin.name = @Login) OR @Login IS NULL) AND
 ((prin.type = 'R' AND prin.name = @Role) OR @Role IS NULL);

/* SQL 2000 */
DECLARE
 @Login varchar(50),
 @Role varchar(50),
 @ObjectName varchar(50);

SET @Login = NULL;
SET @Role = NULL;
SET @ObjectName = '[admin_getStoreOrderTimes]';

SELECT
 CASE ProtectType
 WHEN 204 THEN 'GRANT_W_GRANT'
 WHEN 205 THEN 'GRANT'
 WHEN 206 THEN 'DENY' END
 + SPACE(1)
 + CASE Action
 WHEN 26 THEN 'REFERENCES'
 WHEN 178 THEN 'CREATE FUNCTION'
 WHEN 193 THEN 'SELECT'
 WHEN 195 THEN 'INSERT'
 WHEN 196 THEN 'DELETE'
 WHEN 197 THEN 'UPDATE'
 WHEN 198 THEN 'CREATE TABLE'
 WHEN 203 THEN 'CREATE DATABASE'
 WHEN 207 THEN 'CREATE VIEW'
 WHEN 222 THEN 'CREATE PROCEDURE'
 WHEN 224 THEN 'EXECUTE'
 WHEN 228 THEN 'BACKUP DATABASE'
 WHEN 233 THEN 'CREATE DEFAULT'
 WHEN 235 THEN 'BACKUP LOG'
 WHEN 236 THEN 'CREATE RULE' END
 + SPACE(1)
 + 'ON'
 + SPACE(1)
 + '['
 + USER_NAME(o.uid)
 + '].['
 + OBJECT_NAME(o.id)
 + ']'
 + SPACE(1)
 + 'TO'
 + SPACE(1)
 + '['
 + u.name
 + '];'
 + CHAR(10)
 + 'GO'
 + CHAR(10)
FROM
 sysusers u
INNER JOIN
 sysprotects p ON
 u.uid = p.uid
INNER JOIN
 sysobjects o ON
 p.id = o.id
WHERE
 ((IsLogin = 1 AND u.name = @Login) OR @Login IS NULL) AND
 (((u.IsSQLRole = 1 OR u.IsAppRole = 1) AND u.name = @Role) OR @Role IS NULL) AND
 (OBJECT_NAME(o.id) = @ObjectName OR @ObjectName IS NULL) AND
 u.name NOT IN ('public','db_owner','db_accessadmin','db_securityadmin','db_ddladmin',
 'db_backupoperator','db_datareader','db_datawriter','db_denydatareader',
 'db_denydatawriter','guest');

~Ron