November 3, 2004 at 4:23 pm
I WOULD LIKE TO KNOW HOW CAN I COPY AN USER ROLE AND THEIR PERMISSIONS OF A DATABASE AND ADD THEM TO OTHER DATABASE WITH ALL THEIR PERMISSIONS? I SUPPOSE THAT IT'S POSSIBLE USING T-SQL
I ONLY NEED TO COPY THE ROLE AND THEIR PERMISSIONS
THANKS
November 3, 2004 at 4:29 pm
November 4, 2004 at 3:11 am
We have used the script below to pull off permissions for database roles:
-- Build statements to replicate authorities for a Database Role
SELECT
CASE p.protecttype
WHEN 205 THEN
CASE
WHEN p.action = 26 THEN
'GRANT REFERENCES ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
WHEN p.action = 193 THEN
'GRANT SELECT ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
WHEN p.action = 195 THEN
'GRANT INSERT ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
WHEN p.action = 196 THEN
'GRANT DELETE ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
WHEN p.action = 197 THEN
'GRANT UPDATE ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
WHEN p.action = 224 THEN
'GRANT EXECUTE ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
END
WHEN 206 THEN
CASE
WHEN p.action = 26 THEN
'DENY REFERENCES ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
WHEN p.action = 193 THEN
'DENY SELECT ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
WHEN p.action = 195 THEN
'DENY INSERT ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
WHEN p.action = 196 THEN
'DENY DELETE ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
WHEN p.action = 197 THEN
'DENY UPDATE ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
WHEN p.action = 224 THEN
'GRANT EXECUTE ON ' + SUBSTRING(o.name, 1, 30) + ' TO ' + SUBSTRING(u.name, 1, 30) + ';'
END
END as '-- Grant'
FROM sysobjects o,
sysusers u,
sysprotects p
WHERE o.id = p.id
AND u.uid = p.uid
AND u.issqlrole = 1 -- Include Roles only
AND NOT (o.xtype = 'V' and o.category = 2) -- Exclude INFORMATION schema views
ORDER BY u.name,o.name
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply