bibi92
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hello,
I execute the script with sysadmin privilege.
Thanks
Regards
bibi
I execute the script with sysadmin privilege.
Thanks
Regards
bibi
ASKER
Thanks bibi
remove the quotes around @SchemaSource
AND b.name = @Schsource