October 22, 2011 at 4:01 pm
Comments posted to this topic are about the item Reverse-Engineer Database Permissions (2005 up)
---------------------------------------------------------------------
October 24, 2011 at 7:07 am
Hello
Interesting script but it seems some grant are not included.
I have a database with a role "dev" which I granted
GRANT CREATE PROCEDURE,CREATE FUNCTION,CREATE VIEW TO [DEV_ROLE];
GRANT ALTER ON SCHEMA::[THIS_SCHEMA1] TO [DEV_ROLE];
GRANT SHOWPLAN TO [DEV_ROLE] ;
but only ALTER was restore by your stored procedure
I haven't check why for now
October 24, 2011 at 7:26 am
I added that to find Database permissions
--DATABASE permissions
SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + 'TO [' + USER_NAME(grantee_principal_id)+']' +
case
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
as '-- Database permissions'
FROM sys.database_permissions (NOLOCK)
WHERE class_desc = 'DATABASE' and type !='CO'
ORDER BY USER_NAME(grantee_principal_id),DB_NAME(major_id)
October 24, 2011 at 9:38 am
thank you pmonschein, thats an improvement.
I have been too restrictive in my last select statement in specifying view definition, so just replace that select with your statement. As the proc is within a database I wouldn't include the DB_NAME(major_id) in the order by clause.
also put a space before 'TO' so there is a space in the reverse engineered code, i.e
--DATABASE permissions
SELECT replace(state_desc,'_with_grant_option','') + ' '+ permission_name + ' TO [' + USER_NAME(grantee_principal_id)+']' +
case
when state_desc like '%with_grant_option' then ' with grant option'
else
' '
end
as '-- Database permissions'
FROM sys.database_permissions (NOLOCK)
WHERE class_desc = 'DATABASE' and type !='CO'
ORDER BY USER_NAME(grantee_principal_id)
---------------------------------------------------------------------
December 4, 2012 at 10:05 am
Thanks for this!
The author mentions: "There are a number of scripts out there to do this for SQL2000."
Could someone post a link to one of those scripts for SQL 2000?
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 4, 2012 at 11:35 am
A google search of 'script user permissions sql server 2000' returns a number but attached is one we used to use.
edit - if you found the script useful, please vote on it!
---------------------------------------------------------------------
May 12, 2016 at 6:58 am
Thanks for the script.
Viewing 7 posts - 1 through 6 (of 6 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