Stored Procedures : Execute as owner

Using EXECUTE AS OWNER in a stored procedure definition allows you to raise permissions for the execution of the procedure.

This enables a application login with low privileges to perform owner (dbo) privileged functionality using just the execute permissions on the sproc.

CREATE PROCEDURE dbo.EmptyMyTable

WITH EXECUTE AS OWNER

AS

BEGIN

TRUNCATE dbo.TableA

END

Clay Lenhart : SQL Server Security with EXECUTE AS OWNER

Permissions ! doh!

This one escaped me.

Rather than using system views to generate permissions statements for all objects, you can use the grant statement WITHOUT the ON clause.

Therefore >
GRANT VIEW DEFINITION TO [user]

Rather than repeating this for each procedure >
GRANT VIEW DEFINITION ON sp_name TO [user]

Using it for the EXECUTE permission makes this piece of code redundant.

Developer Permissions TSQL

to view activity monitor >
USE MASTER; GRANT VIEW SERVER STATE TO [username]

to run profiler >
USE MASTER; GRANT ALTER TRACE TO [username]

to view execution plans >
USE DBNAME; GRANT SHOWPLAN TO [username]

SQL 2005 : Permissions

A brilliant permissions comparison script posted today by Carley Zepada on SQLServerCentral (Site requires registration).

Querying SQL 2000 Table Permissions

Since I am auditing old sql 2000 instances, these methods are necessary

Method 1 : The appropriate information_schema view.

select 
 grantor, 
 grantee, 
 table_schema+'.'+table_name as objectname, 
 privilege_type 
from 
 information_schema.table_privileges

Method 2 : Capturing the output of sp_helpprotect
(nb : sp_helpprotect can be passed parameters - http://msdn2.microsoft.com/en-us/library/ms190310.aspx)

create table #helpprotect_results (
      owner sysname,
      object sysname,
      grantee sysname,
      grantor sysname,
      protecttype nvarchar(10),
      action nvarchar(20),
      columnname sysname )

insert into #helpprotect_results exec sp_helprotect

select grantor,
    grantee,
       owner + '.' + object as objectname,
       protecttype,
       action
from   #helpprotect_results
order by grantee

drop table #helpprotect_results

Method 3 : Good old system tables

select
 user_name(sec.grantor) as grantor,
 user_name(sec.uid) as grantee,
 stbl.name + '.' + obj.name as objectname,
 case obj.type
  when 'C' then 'Check constraint'
  when 'D' then 'Default (constraint or stand-alone)'
  when 'F' then 'Foreign Key constraint'
  when 'PK' then 'Primary Key constraint'
  when 'P' then 'SQL Stored procedure'
  when 'FN' then 'SQL scalar function'
  when 'R' then 'Rule (old-style, stand-alone)'
  when 'RF' then 'Replication-filter-procedure'
  when 'S' then 'System base table'
  when 'TA' then 'assembly (CLR) DML trigger'
  when 'TF' then 'SQL table-valued-function'
  when 'U' then 'Table (user-defined)'
  when 'UQ' then 'Unique constraint'
  when 'V' then 'View'
  when 'X' then 'Extended stored procedure'
 end as objtype,
 protecttype.name permission_level, 
 action.name as permission
from
 dbo.sysobjects as obj
inner join sysusers as stbl on stbl.uid = obj.uid
inner join sysprotects as sec on sec.id = obj.id
inner join master.dbo.spt_values as action on sec.action = action.number and action.type = 't'
inner join master.dbo.spt_values as protecttype on sec.protecttype = protecttype.number and protecttype.type = 't'
where 
 obj.type in ('U','V','P','FN','X') 

Adding server logins to current database

Loops login names and ;
1) grants database access
2) adds db_datareader role
3) adds db_datawriter role

Includes 2 login lists, either all or logins owning sql agent jobs (uncomment as appropriate)

DECLARE @sql NVARCHAR(MAX)
DECLARE @login VARCHAR(200)
DECLARE CURSORNAME CURSOR FOR
-- all logins >

  select [name] from master.dbo.syslogins
 where [name] <> 'sa'
 and [language] is not null  

-- logins that own jobs! >

-- SELECT logins.[name]
-- FROM msdb.dbo.sysjobs jobs
-- INNER JOIN master.dbo.syslogins logins
-- ON jobs.owner_sid = logins.sid

OPEN CURSORNAME
FETCH NEXT FROM CURSORNAME INTO @login

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql = 'CREATE USER [dummyuser] FOR LOGIN [dummyuser]
   EXEC sp_addrolemember N''db_datareader'', N''dummyuser''
   EXEC sp_addrolemember N''db_datawriter'', N''dummyuser''  '

SET @sql = REPLACE(@sql,'dummyuser',@login)

print @sql
exec sp_executesql @sql
FETCH NEXT FROM CURSORNAME INTO @login
END

CLOSE CURSORNAME
DEALLOCATE CURSORNAME

GO

Grant Execute to all Stored Procedures

Solution 1 : Dynamic SQL -

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO LoginName' -- Replace LoginName with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

Solution 2 : Procedure -

CREATE PROCEDURE USP_gen_CreateGrants AS
DECLARE @ExecSQL varchar(100)
DECLARE curGrants CURSOR FOR

SELECT 'GRANT EXECUTE ON ' + NAME + ' TO MyLogin' -- Replace MyLogin with the name of your new Login
FROM SYSOBJECTS
WHERE TYPE = 'P'
AND LEFT(NAME,2) <> 'sp' -- system procs
AND LEFT(NAME,2) <> 'dt' -- VSS procs

OPEN curGrants
FETCH NEXT FROM curGrants
INTO @ExecSQL

WHILE @@FETCH_STATUS = 0
BEGIN
Exec(@ExecSQL)
IF @@ERROR <> 0
BEGIN
RETURN 1 -- return 1 if there is an error
END
Print @ExecSQL
FETCH NEXT FROM curGrants INTO @ExecSQL
END

CLOSE curGrants
DEALLOCATE curGrants

USP_EffectiveLoginPermissions

Procedure to show login permissions for all databases on a server.
Create the Procedure in master db or your tools database.

EXEC USP_EffectiveLoginPermissions ''
go

CREATE PROCEDURE USP_EffectiveLoginPermissions
          @User VARCHAR(100)
AS
  BEGIN
  
    SET NOCOUNT ON
    
    IF OBJECT_ID('tempdb..##ServerLogins') IS NOT NULL
      BEGIN
        DROP TABLE ##SERVERLOGINS
      END
      
    IF OBJECT_ID('tempdb..##DBUsers') IS NOT NULL
      BEGIN
        DROP TABLE ##DBUSERS
      END
      
    IF OBJECT_ID('tempdb..##DBList') IS NOT NULL
      BEGIN
        DROP TABLE ##DBLIST
      END
          
    IF OBJECT_ID('tempdb..##Results') IS NOT NULL
      BEGIN
        DROP TABLE ##RESULTS
      END
      
    DECLARE  @DBName VARCHAR(128);    
    DECLARE  @SQLCmd VARCHAR(2000);    
    DECLARE  @NumberOfDBs INT;
    
    -- Get the SQL Server logins
    -- Create login table
    CREATE TABLE ##SERVERLOGINS (
  [SID] VARBINARY(85) NULL,
  [LOGIN_NAME] VARCHAR(128) NULL);
    
    -- Populate login table
    INSERT INTO ##SERVERLOGINS
    SELECT SID,CAST(LOGINNAME AS VARCHAR(128))  AS [LOGIN_NAME]
    FROM   MASTER.DBO.SYSLOGINS
    
    
    -- Create list of databases
    CREATE TABLE ##DBLIST (
  [DBNAME] VARCHAR(128))
    
    -- perform for all dbs on server
    INSERT INTO ##DBLIST
    SELECT   [NAME]
    FROM     MASTER..SYSDATABASES
    WHERE    [DBID] > 4
    ORDER BY [NAME];
    
    SELECT @NumberOfDBs = COUNT(* )
    FROM   ##DBLIST
           
    -- Create the output table for the Database User ID's
    CREATE TABLE ##DBUSERS (
  [DATABASE_USER_ID] VARCHAR(128),
  [SERVER_LOGIN] VARCHAR(128),
  [DATABASE_ROLE] VARCHAR(128),
  [DATABASE_NAME] VARCHAR(128));  
      
    -- Declare a cursor to loop through all the databases on the server
    DECLARE CSRDB CURSOR  FOR
  SELECT   [DBNAME]
  FROM     ##DBLIST
  ORDER BY [DBNAME]
             
    -- Open the cursor and get the first database name
    OPEN CSRDB
    
    FETCH NEXT FROM CSRDB
    INTO @DBName
         
    -- Loop through the cursor
    WHILE @@FETCH_STATUS = 0
      BEGIN      
        -- populate ##DBUsers table for current db
        SELECT @SQLCmd = 'INSERT ##DBUsers ' 
   + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], ' 
   + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], ' 
   + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],' 
   + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]' 
   + ' FROM [' + @DBName + '].[dbo].[sysusers] su' 
   + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid' 
   + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' 
   + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' 
   + ' ON sm.groupuid = sug.uid)' 
   + ' ON su.uid = sm.memberuid ' 
   + ' WHERE su.hasdbaccess = 1' 

IF LTRIM(RTRIM(@User)) <> ''
  SELECT @SQLCmd = @SQLCmd + ' AND SU.name = ''' + @User + ''''

   -- uncomment to debug       
        -- PRINT @SQLCmd
        EXEC(@SQLCmd)
        
        -- Get the next database name
        FETCH NEXT FROM CSRDB
        INTO @DBName
             
      -- End of the cursor loop
      END
      
    -- Close and deallocate the CURSOR
    CLOSE CSRDB
    
    DEALLOCATE CSRDB
             
    CREATE TABLE ##RESULTS (
        [INSTANCE_NAME] VARCHAR(128),
  [DATABASE_NAME] VARCHAR(128),
  [OBJTYPE] VARCHAR(128),
  [LOGIN_STATUS] VARCHAR(128),
  [USERGRANTEE] VARCHAR(128));
    
    TRUNCATE TABLE ##RESULTS
    
    --data for logins
    INSERT INTO ##RESULTS
    SELECT 
  CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) AS INSTANCE_NAME, 
  DATABASE_NAME,
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datareader' THEN 'db_datareader, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_accessadmin' THEN 'db_accessadmin, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_backupoperator' THEN 'db_backupoperator, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datawriter' THEN 'db_datawriter, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_ddladmin' THEN 'db_ddladmin, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatareader' THEN 'db_denydatareader, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatawriter' THEN 'db_denydatawriter, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_owner' THEN 'db_owner, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'db_securityadmin' THEN 'db_securityadmin, ' ELSE '' END) + 
  MAX(CASE [DATABASE_ROLE] WHEN 'public' THEN 'public, ' ELSE '' END) AS ROLE_OBJECTTYPE,
  CASE [SERVER_LOGIN] WHEN '* Orphaned *' THEN [SERVER_LOGIN] ELSE 'OK' END AS LOGIN_STATUS,
        CASE WHEN [DATABASE_USER_ID] = [SERVER_LOGIN] THEN [DATABASE_USER_ID] 
    WHEN [SERVER_LOGIN] = '* Orphaned *' THEN [DATABASE_USER_ID] 
    ELSE [DATABASE_USER_ID] + ' (' + [SERVER_LOGIN] + ')' END AS USER_GRANTEE
  
    FROM     
  ##DBUSERS
    GROUP BY 
  [DATABASE_NAME],
  [DATABASE_USER_ID],
  [SERVER_LOGIN]
                            
    SELECT   
  [INSTANCE_NAME], 
  [DATABASE_NAME],
  [USERGRANTEE],
  LEFT(OBJTYPE,LEN([OBJTYPE])-1) AS ROLES,
  [LOGIN_STATUS]
    FROM     
  ##RESULTS
    ORDER BY 
  1, 2, 3
  END
  go

USP_EffectivePermissions

Procedure to report on server & object permissions for a given user.
Usage : EXEC USP_EffectivePermissions 'username'
Install in master db to use from any db on the server.

CREATE PROC USP_EffectivePermissions
         @User VARCHAR(100)
AS
 BEGIN

   SET NOCOUNT ON
  
   IF OBJECT_ID('tempdb..##ServerLogins') IS NOT NULL
     BEGIN
       DROP TABLE ##SERVERLOGINS
     END
    
   IF OBJECT_ID('tempdb..##DBUsers') IS NOT NULL
     BEGIN
       DROP TABLE ##DBUSERS
     END
    
   IF OBJECT_ID('tempdb..##DBList') IS NOT NULL
     BEGIN
       DROP TABLE ##DBLIST
     END
    
   IF OBJECT_ID('tempdb..##DBObjPermissions') IS NOT NULL
     BEGIN
       DROP TABLE ##DBOBJPERMISSIONS
     END
    
   IF OBJECT_ID('tempdb..##CrossJoinMultiplier') IS NOT NULL
     BEGIN
       DROP TABLE ##CROSSJOINMULTIPLIER
     END
    
   IF OBJECT_ID('tempdb..##Results') IS NOT NULL
     BEGIN
       DROP TABLE ##RESULTS
     END
    
   DECLARE  @DBName VARCHAR(128);   
   DECLARE  @SQLCmd VARCHAR(2000);   
   DECLARE  @NumberOfDBs INT;
  
   -- Get the SQL Server logins
   -- Create login table
   CREATE TABLE ##SERVERLOGINS ([SID] VARBINARY(85) NULL,[LOGIN_NAME] VARCHAR(128) NULL);
  
   -- Populate login table
   INSERT INTO ##SERVERLOGINS
   SELECT SID,CAST(LOGINNAME AS VARCHAR(128))  AS [LOGIN_NAME]
   FROM   MASTER.DBO.SYSLOGINS
   WHERE  LOGINNAME = @User;
  
   -- Create list of databases
   CREATE TABLE ##DBLIST (
  [DBNAME] VARCHAR(128))
  
   -- perform for all dbs on server
   INSERT INTO ##DBLIST
   SELECT   NAME
   FROM     MASTER..SYSDATABASES
   WHERE    DBID > 4
   ORDER BY NAME;
  
   SELECT @NumberOfDBs = COUNT(* )
   FROM   ##DBLIST
         
   -- Create the output table for the Database User ID's
   CREATE TABLE ##DBUSERS (
  [DATABASE_USER_ID] VARCHAR(128),
  [SERVER_LOGIN] VARCHAR(128),
  [DATABASE_ROLE] VARCHAR(128),
  [DATABASE_NAME] VARCHAR(128));
  
   -- Create the output table for Object Level Permissions
   CREATE TABLE ##DBOBJPERMISSIONS (
  [DATABASE_NAME] VARCHAR(128),
  [GRANTOR] VARCHAR(128),
  [GRANTEE] VARCHAR(128),
  [OBJTYPE] VARCHAR(128),
  [OBJECTNAME] VARCHAR(128),
  [PERMISSION_LEVEL] VARCHAR(128),
  [PERMISSION] VARCHAR(128));
     
   -- Declare a cursor to loop through all the databases on the server
   DECLARE CSRDB CURSOR  FOR
  SELECT   DBNAME
  FROM     ##DBLIST
  ORDER BY DBNAME
           
   -- Open the cursor and get the first database name
   OPEN CSRDB
  
   FETCH NEXT FROM CSRDB
   INTO @DBName
       
   -- Loop through the cursor
   WHILE @@FETCH_STATUS = 0
     BEGIN     
       -- populate ##DBUsers table for current db
       SELECT @SQLCmd = 'INSERT ##DBUsers '
   + ' SELECT CAST(su.[name] AS VARCHAR(128)) AS [database_user_id], '
   + ' CAST(COALESCE (u.[login_name], ''* Orphaned *'') AS VARCHAR(128))AS [server_login], '
   + ' CAST(COALESCE (sug.name, ''Public'') AS VARCHAR(128)) AS [database_role],'
   + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name]'
   + ' FROM [' + @DBName + '].[dbo].[sysusers] su'
   + ' LEFT OUTER JOIN ##ServerLogins u' + ' ON su.sid = u.sid'
   + ' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm '
   + ' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug '
   + ' ON sm.groupuid = sug.uid)'
   + ' ON su.uid = sm.memberuid '
   + ' WHERE su.hasdbaccess = 1'
   + ' AND SU.name = ''' + @User + ''''
  -- uncomment to debug      
       -- PRINT @SQLCmd
       EXEC(@SQLCmd)
      
       -- populate ##DBObjPermissions table for current db
       SELECT @SQLCmd = 'use [' + @DBName + '];'
   + 'insert into ##DBObjPermissions '
   + ' select '
   + ' CAST(''' + @DBName + ''' AS VARCHAR(128)) AS [database_name],'
   + ' user_name(sec.grantor) as grantor, '
   + ' user_name(sec.uid) as grantee, '
   + ' case obj.type '
   + ' when ''C'' then ''Check constraint'' '
   + ' when ''D'' then ''Default (constraint or stand-alone)'' '
   + ' when ''F'' then ''Foreign Key'' '
   + ' when ''PK'' then ''Primary Key'' '
   + ' when ''P'' then ''Stored Procedure'' '
   + ' when ''FN'' then ''Function (Scalar)'' '
   + ' when ''IF'' then ''Function (Inline)'' '
   + ' when ''R'' then ''Rule (old-style, stand-alone)'' '
   + ' when ''RF'' then ''Replication-filter-procedure'' '
   + ' when ''S'' then ''System base table'' '
   + ' when ''TA'' then ''Assembly (CLR) DML trigger'' '
   + ' when ''TF'' then ''Function (TableValued)'' '
   + ' when ''U'' then ''Table'' '
   + ' when ''UQ'' then ''Unique constraint'' '
   + ' when ''V'' then ''View'' '
   + ' when ''X'' then ''Extended stored procedure'' '
   + ' end as objtype, '
   + ' stbl.name + ''.'' + obj.name as objectname, '
   + ' protecttype.name permission_level,  ' + ' action.name as permission '
   + ' from ' + '[' + @DBName + '].dbo.sysobjects as obj '
   + ' inner join [' + @DBName + '].dbo.sysusers as stbl on stbl.uid = obj.uid '
   + '        and stbl.name = ''' + @User + ''''
   + ' inner join ##DBUsers on ##DBUsers.[database_user_id] COLLATE SQL_Latin1_General_CP1_CS_AS = stbl.name COLLATE SQL_Latin1_General_CP1_CS_AS'
   + ' inner join [' + @DBName + '].dbo.sysprotects as sec on sec.id = obj.id '
   + ' inner join master.dbo.spt_values as action on sec.action = action.number and action.type = ''t'' '
   + ' inner join master.dbo.spt_values as protecttype on sec.protecttype = protecttype.number and protecttype.type = ''t'' '
   + ' where objectpropertyex(obj.id,''ismsshipped'') = 0 '
                     
  -- uncomment to debug                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
       -- PRINT @SQLCmd
       EXEC(@SQLCmd)
      
       -- Get the next database name
       FETCH NEXT FROM CSRDB
       INTO @DBName
           
     -- End of the cursor loop
     END
    
   -- Close and deallocate the CURSOR
   CLOSE CSRDB
  
   DEALLOCATE CSRDB
  
   CREATE TABLE ##CROSSJOINMULTIPLIER (
     USERNAME VARCHAR(128)   NULL);
  
   IF @NumberOfDBs = 1
     INSERT INTO ##CROSSJOINMULTIPLIER
     SELECT [SERVER_LOGIN]
     FROM   ##DBUSERS -- 1 db, multiply section headers by users
   ELSE
     INSERT INTO ##CROSSJOINMULTIPLIER
     SELECT TOP 1 'dummy'
     FROM   SYSOBJECTS -- multiple dbs,
           
   CREATE TABLE ##RESULTS (
     [HEADER_ROW] INT,[DATABASE_NAME] VARCHAR(128),[SORT_ORDER] INT,[GRANTOR] VARCHAR(128),[GRANTEE] VARCHAR(128),[OBJTYPE] VARCHAR(128),[OBJECTNAME] VARCHAR(128),[USERGRANTEE] VARCHAR(128),[PERMISSION_LEVEL] VARCHAR(128),[PERMISSION] VARCHAR(128));
  
   TRUNCATE TABLE ##RESULTS
  
   --data for logins
   INSERT INTO ##RESULTS
   SELECT
  0 AS HEADER_ROW,
  CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DATABASE_NAME AS DATABASE_NAME,
  2 AS SORT_ORDER,
  '' AS GRANTOR,
  '' AS GRANTEE,
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datareader' THEN 'db_datareader, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_accessadmin' THEN 'db_accessadmin, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_backupoperator' THEN 'db_backupoperator, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_datawriter' THEN 'db_datawriter, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_ddladmin' THEN 'db_ddladmin, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatareader' THEN 'db_denydatareader, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_denydatawriter' THEN 'db_denydatawriter, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_owner' THEN 'db_owner, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'db_securityadmin' THEN 'db_securityadmin, ' ELSE '' END) +
  MAX(CASE [DATABASE_ROLE] WHEN 'public' THEN 'public, ' ELSE '' END) AS ROLE_OBJECTTYPE,
       'database user' AS OBJ_NAME,
  CASE [SERVER_LOGIN] WHEN [DATABASE_USER_ID] THEN [DATABASE_USER_ID] ELSE [DATABASE_USER_ID] + ' (' + [SERVER_LOGIN] + ')' END AS USER_GRANTEE,
  '' AS PERMISSION_LEVEL,
  '' AS PERMISSION
   FROM    
  ##DBUSERS
   GROUP BY
  [DATABASE_NAME],
  [DATABASE_USER_ID],
  [SERVER_LOGIN]
           
   -- data for objects
   INSERT INTO ##RESULTS
   SELECT  0 AS HEADER_ROW,
   CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DATABASE_NAME AS DATABASE_NAME,
   4 AS SORT_ORDER,
   GRANTOR,
   GRANTEE,
   [OBJTYPE] AS ROLE_OBJECTTYPE,
   [OBJECTNAME] AS OBJ_NAME,
   [GRANTEE] AS USER_GRANTEE,
   [PERMISSION_LEVEL] AS PERMISSION_LEVEL,
   [PERMISSION] AS PERMISSION
   FROM   ##DBOBJPERMISSIONS
         
   INSERT INTO ##RESULTS
   SELECT  1 AS HEADER_ROW,
   CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DBNAME AS DATABASE_NAME,
   1 AS SORT_ORDER,
   '' AS GRANTOR,
   '' AS GRANTEE,
   'Role' AS ROLE_OBJECTTYPE,
   'Object' AS OBJ_NAME,
   'User' AS USER_GRANTEE,
   ' ' AS PERMISSION_LEVEL,
   ' ' AS PERMISSION
   FROM   ##DBLIST
          INNER JOIN ##RESULTS
            ON ##RESULTS.DATABASE_NAME = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + ##DBLIST.DBNAME
               AND ##RESULTS.SORT_ORDER = 2
                                         
   -- header rows for objects
   INSERT INTO ##RESULTS
   SELECT  1 AS HEADER_ROW,
   CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + DBNAME AS DATABASE_NAME,
   3 AS SORT_ORDER,
   '' AS GRANTOR,
   '' AS GRANTEE,
   'Object Type' AS ROLE_OBJECTTYPE,
   'Object' AS OBJ_NAME,
   'Grantee' AS USER_GRANTEE,
   'Permission Level' AS PERMISSION_LEVEL,
   'Permission' AS PERMISSION
   FROM   ##DBLIST
          INNER JOIN ##RESULTS
            ON ##RESULTS.DATABASE_NAME = CAST(SERVERPROPERTY('ServerName') AS VARCHAR(50)) + ' - ' + ##DBLIST.DBNAME
               AND ##RESULTS.SORT_ORDER = 4
                                         
   SELECT  
  DATABASE_NAME,
  GRANTOR,
  GRANTEE,
  OBJTYPE,
  OBJECTNAME,
  USERGRANTEE,
  PERMISSION_LEVEL,
  PERMISSION
   FROM    
  ##RESULTS
   ORDER BY
  DATABASE_NAME,
  SORT_ORDER
 END
 go

SQL 2005 : CLR - External Access permissions

Msg 6522, Level 16, State 1, Procedure SP_Backup, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "SP_Backup":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at DatabaseName.dbo.SP_Backup(Parameter1, Parmeter2, Parameter3...)

The CLR stored procedure is using XP_CMDShell to access the file system.

The permissions on the assembly therefore need to be increased to allow this.


Changing assembly permissions in Management Studio -

1) Navigate to (databasename) > Programmability > Assemblies > (expand)
2) Double click assembly name to get assembly properties.
3) Set permission set of assembly to 'external access'.


Changing assembly permissions in tsql -

GRANT EXTERNAL ACCESS ASSEMBLY TO AssemblyName

sp_changeobjectowner

Changes the owner of an object (table/view/sp etc) -

use :
sp_changeobjectowner [objectname] , [new owner]

example :

sp_changeobjectowner 'fasttrackuser.FastTrackPackageDocumentsCombinations', 'dbo'

remarks :
must have appropriate permissions to perform this action.

Stored procedure permissions script

select 'Grant EXEC on ' + name + ' to MyUser' 
  from sysobjects 
 where type = 'P'