Link to home
Start Free TrialLog in
Avatar of bibi92
bibi92Flag for France

asked on

Script all tables permissions

Hello,

I try to script all permissions tables when I run the following query, the result is 4045 rows :
select c.action, o.name, b.name from sys.objects o
INNER JOIN sysprotects c
      ON o.object_id = c.id
INNER JOIN sysusers b
      ON c.uid = b.uid
WHERE o.type IN ('U','P','V')
and b.name = 'F_ADX'
 
ORDER BY o.name

When I try to do the same thing with stored prod and schemaname F_ADX, the result is 0 row

create procedure [dbo].[create_grants]
@Dbname varchar (30),  
@Schsource varchar (30),  
@NewSchema      varchar(30)  
 
AS  
set nocount on
SELECT 'GRANT '+
      CASE c.action
            WHEN 26 THEN 'REFERENCES'
            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 233 THEN 'CREATE DEFAULT'
            ELSE 'CREATE RULE'
      END+ ' ON '+ o.name+ ' TO ' + @NewSchema  + CHAR(13) + ';' + CHAR(13)
FROM sys.objects o
INNER JOIN sysprotects c
      ON o.object_id = c.id
INNER JOIN sysusers b
      ON c.uid = b.uid
WHERE o.type IN ('U','P','V')
AND b.name = '@Schsource'
 
ORDER BY o.name

Thanks
Regards
bibi
Avatar of Aneesh
Aneesh
Flag of Canada image

AND b.name = '@Schsource'  
 
remove the quotes around @SchemaSource
AND b.name = @Schsource
 


ASKER CERTIFIED SOLUTION
Avatar of Shannon_Lowder
Shannon_Lowder
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bibi92

ASKER

Hello,

I execute the script with sysadmin privilege.

Thanks

Regards

bibi
Avatar of bibi92

ASKER

Thanks bibi