Posted by: Karlo Bartels | 2008/11/26

Permissions

Last week I ran into some permission problems while running a change script on a client’s acceptance server. I assumed the script also contained the object’s access permissions which I specified on my development machine, but I was wrong.

While reviewing the DELETE permission denied on object X messages, I decided to create a script that would list object permissions and generate an executable script for the target database.

Of course, this script would only make sense if you had applied an object change script on the target database first, but that being said, let’s look at the script:

Permissions script for SQL Server 2005 and 2008

Click here to download a properly formatted version

----------------------------------------------------------------------
-- This script generates a permissions script for a specific user or
-- database role. The script comes in handy when transferring
-- existing permissions to a new database.
--
-- Run this script on the source database to generate the permissions script.
-- Select the item 'Results to Text' in the 'Query | Results To' menu
-- to generate executable script. Copy the text in the results pane
-- to a new query window in the target database and run it.
--
-- NOTE: This script works on SQL Server 2005 and 2008.
-- There is also a SQL Server 2000 version of this script.
-- See https://linktosql.wordpress.com/2008/11/26/permissions/ for more information.
-----------------------------------------------------------------------
USE [YourDatabaseNameHere]
GO
SET NOCOUNT ON;
GO
DECLARE @UserOrDatabaseRole nvarchar(128)
-- Set this variable to the user or database role for which to create the script
SET @UserOrDatabaseRole = '[YourUserOrRoleHere]'
;
 
-- Create temporary table to hold the script
CREATE TABLE #Script(ScriptLine varchar(4000
));
 
-- Generate permission script
INSERT INTO #Script
SELECT CASE WHEN pm.state = 'W' THEN 'GRANT' ELSE pm.state_desc END + ' ' + pm.permission_name + ' ON [' + sch.name + '].[' + obj.name +
'] TO [' + usr.name + ']' + CASE WHEN pm.state = 'W' THEN ' WITH GRANT OPTION ' ELSE '' END + CHAR(10)+ 'GO'
FROM   sys.database_permissions AS pm INNER JOIN sys.objects AS obj
ON     pm.major_id = obj.object_id INNER JOIN sys.database_principals AS usr ON pm.grantee_principal_id = usr.principal_id LEFT OUTER JOIN sys.columns AS cl ON cl.column_id = pm.minor_id AND cl.object_id = pm.major_id INNER JOIN sys.schemas AS sch ON obj.schema_id = sch.schema_id WHERE usr.name = @UserOrDatabaseRole ORDER BY obj.name
;
 
-- Return script to client
SELECT ScriptLine AS Run_this_on_the_target_database FROM #Script
;
 
-- Clean-up
DROP TABLE #Script
;
 

Permissions script for SQL Server 2000

Click here to download a properly formatted version

----------------------------------------------------------------------
-- This script generates a permissions script for a specific user or
-- database role. The script comes in handy when transferring
-- existing permissions to a new database.
--
-- Run this script on the source database to generate the permissions script.
-- Select the item 'Results to Text' in the 'Query | Results To' menu
-- to generate executable script. Copy the text in the results pane
-- to a new query window in the target database and run it.
--
-- NOTE: This script works on SQL Server 2000 only. There is also a
-- SQL Server 2005 / 2008 version of this script.
-- See https://linktosql.wordpress.com/2008/11/26/permissions/ for more information.
-----------------------------------------------------------------------
USE [YourDatabaseNameHere]
GO
SET NOCOUNT ON;
GO
DECLARE @UserOrDatabaseRole nvarchar(128)
-- Set this variable to the user or database role for which to create the script
SET @UserOrDatabaseRole = '[YourUserOrRoleHere]'
;
-- Create temporary table to hold the script
CREATE TABLE #Script(ScriptLine varchar(4000
));
-- Generate permissions script
INSERT INTO #Script
SELECT CASE
       
WHEN sysprotects.protecttype = 204 THEN 'GRANT '
       
WHEN sysprotects.protecttype = 205 THEN 'GRANT '
       
WHEN sysprotects.protecttype = 206 THEN 'DENY '
  
END +
  
CASE
       
WHEN sysprotects.action = 26 THEN 'REFERENCES'
       
WHEN sysprotects.action = 178 THEN 'CREATE FUNCTION'
       
WHEN sysprotects.action = 193 THEN 'SELECT'
       
WHEN sysprotects.action = 195 THEN 'INSERT'
       
WHEN sysprotects.action = 196 THEN 'DELETE'
       
WHEN sysprotects.action = 197 THEN 'UPDATE'
       
WHEN sysprotects.action = 203 THEN 'CREATE DATABASE'
       
WHEN sysprotects.action = 207 THEN 'CREATE VIEW'
       
WHEN sysprotects.action = 222 THEN 'CREATE PROCEDURE'
       
WHEN sysprotects.action = 224 THEN 'EXECUTE'
       
WHEN sysprotects.action = 228 THEN 'BACKUP DATABASE'
       
WHEN sysprotects.action = 233 THEN 'CREATE DEFAULT'
       
WHEN sysprotects.action = 235 THEN 'BACKUP LOG'
       
WHEN sysprotects.action = 236 THEN 'CREATE RULE'
  
END +
  
' ON [' +
   (
SELECT SU.name FROM sysusers SU WHERE SU.uid = sysobjects.uid) +
  
'].[' + sysobjects.name + '] TO [' + sysusers.name + ']' +
  
CASE WHEN sysprotects.protecttype = 204 THEN ' WITH GRANT OPTION '   ELSE '' END + CHAR(10)+ 'GO'
FROM   sysusers FULL JOIN sysobjects
ON     (sysobjects.xtype IN ('P', 'U') AND sysobjects.Name NOT LIKE 'dt%') LEFT JOIN sysprotects ON sysprotects.uid = sysusers.uid AND sysprotects.id = sysobjects.id
WHERE sysusers.name = @UserOrDatabaseRole ORDER BY sysobjects.name;
-- Clean-up
DROP TABLE #Script
  
 
-- Return script to client
SELECT ScriptLine AS Run_this_on_the_target_database FROM #Script;

 

Please note: select the item Results to Text in the Query | Results To menu to generate executable script that you can run on the target machine.

Download properly formatted version (SQL Server 2005, 2008)

Download properly formatted version (SQL Server 2000)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: