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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy