January 17, 2012 at 5:25 pm
Is there a similar command as the one for table to copy everything from a role to another role?
For a table, it would be as simple as : SELECT * INTO dbo.Contacts_index FROM Person.Contact;
The database has many schemas.
There are many users and they are grouped by different permission (insert, delete, update and select) on the schemas.
I want to make a role for each group and it would be faster if I could copy the first role then changes one or two schema permission in it...
Any idea?
January 17, 2012 at 5:59 pm
Are you trying to create the role through the GUI?
If so, there is a script button that will script out that role. Make your modifications to the script and then execute for the different roles.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2012 at 7:05 pm
What "GUI" are you talking about?
I am using SQL server Management Studio. There is the menu (clicking on the right button of the mouse) with "Script data base role" then I can choose "Create to query or file", but all it gives me in the script is "CREATE ROLE [roleName] AUTHORIZATION [schemaName]" so it is useless.
What I want is to create a new role being the copy of an other role, with all the "securables" sets like the explicit permissions "Grant on SELECT", "Grant on INSERT", "Grant on UPDATE", "Grant on DELETE" for HumanRessources schema (example from AdventureWorks2008).
Wait! I found the "button" at the top of the property window! ... but all it says is "There is no action to be scripted". What am I doing wrong?
January 17, 2012 at 7:46 pm
In order to get the GRANT permissions you will need to query the system views (either server_permissions or database_permissions) depending on which level you need.
I believe this [/url]tip from K. Brian Kelley (script is at the bottom) should help you out.
If you are interested, I did blog about grabbing this information through the GUI window[/url] (properties window in SSMS). My example was using a specific login but it should work for a role as well.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 17, 2012 at 7:52 pm
Shawn Melton (1/17/2012)
... grabbing this information through the GUI window[/url] (properties window in SSMS). My example was using a specific login but it should work for a role as well.
It is this GUI window that I was talking about.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 17, 2012 at 8:06 pm
SQLRNNR (1/17/2012)
It is this GUI window that I was talking about.
Ah. Ok, but the OP actually wants to create a new role from the permissions of a current role. So doing that from the GUI would not really work since if you open up an unchanged object SSMS will not script it out for you.
So the best way to get it would be to script out finding the permissions.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 17, 2012 at 8:06 pm
Hi Shawn
Thanks for the info, but it is not what I am looking for.
What I am looking for is the script from the role to be able to create a similar other one.
I guess I have to script it myself once then copy it with the different options for different roles...
How do we create a role with the schema permission again?
January 17, 2012 at 8:10 pm
SQLRNNR (1/17/2012)
Shawn Melton (1/17/2012)
... grabbing this information through the GUI window[/url] (properties window in SSMS). My example was using a specific login but it should work for a role as well.It is this GUI window that I was talking about.
Ya, I found it (edit my reply, but I guest you did not see it)
If I try the buton, all it says is "There is no action to be scripted".
What am I doing wrong?
I try on a table and it works fine...:unsure:
January 17, 2012 at 8:11 pm
While this is not exactly what you need, it is a start and you maybe able to modify the script to fit your needs
http://www.sqlservercentral.com/scripts/Security/66129/
Miguel
January 17, 2012 at 8:23 pm
Wait! it does not works fine for the table either.
I think the best way to do what I want is to forget about the GUI and the copy and create the command with the grant permission ...
So it would be something like...
CREATE ROLE [TilewRole] AUTHORIZATION [dbo]
then what?
...
Find it!
GRANT SELECT ON SCHEMA::dbo to TilewRole
GRANT UPDATE ON SCHEMA::dbo to TilewRole
GRANT DELETE ON SCHEMA::dbo to TilewRole
GRANT INSERT ON SCHEMA::dbo to TilewRole
Easy! It is not as easy as a "SELECT * INTO...", but I will survive! 😛
Thanks to every one.
Talking to you led me to a solution and that is fantastic!
January 17, 2012 at 11:09 pm
tilew-948340 (1/17/2012)
SQLRNNR (1/17/2012)
Shawn Melton (1/17/2012)
... grabbing this information through the GUI window[/url] (properties window in SSMS). My example was using a specific login but it should work for a role as well.It is this GUI window that I was talking about.
Ya, I found it (edit my reply, but I guest you did not see it)
If I try the buton, all it says is "There is no action to be scripted".
What am I doing wrong?
I try on a table and it works fine...:unsure:
Try this script
SELECTCASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END
+ SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
+ CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END
+ SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(usr.name) COLLATE database_default
+ CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END + ';' AS '--Object Level Permissions'
FROMsys.database_permissions AS perm
INNER JOIN
sys.objects AS obj
ON perm.major_id = obj.[object_id]
INNER JOIN
sys.database_principals AS usr
ON perm.grantee_principal_id = usr.principal_id
LEFT JOIN
sys.columns AS cl
ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
ORDER BY perm.permission_name ASC, perm.state_desc ASC
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 18, 2012 at 8:14 pm
SQLRNNR (1/17/2012)
Try this script
WOW! That is impressive! I had no idea I could do that!
But, sorry... it is still not what I was looking for... (but it is really a good thing to know!)
If I execute your query on the role "TilewRole"(see later in the text), it gives permission on sys. syallocunits, sysrowsets and sysallocunits and if I execute it, it gives the error that sys.sysallocunits does not exists or that I don't have the permission.
GRANT DELETE ON [sys].[sysallocunits] TO [TilewRole];
GRANT INSERT ON [sys].[sysallocunits] TO [TilewRole];
GRANT SELECT ON [sys].[sysallocunits] TO [TilewRole];
GRANT SELECT ON [sys].[sysrowsets] TO [TilewRole];
GRANT UPDATE ON [sys].[sysallocunits] TO [TilewRole];
But I am still working to understand what your query do, so maybe I'll find the way to what I want.:cool:
For the moment, I did a query with 4 lines per possible schema for the role with the "grant", "deny", "insert" and "update" permission
Now, all I have to do, instead of using the GUI, I use the "replace all" tool to change the name for the new role
and comments (or uncomments) the lines I want or not as permission...
My Query file would look like that:
use adventureworks2008;
CREATE ROLE [TilewRole] AUTHORIZATION [dbo]
-- #### schema Production ####
GRANT SELECT ON SCHEMA::Production to TilewRole
GRANT UPDATE ON SCHEMA::Production to TilewRole
GRANT DELETE ON SCHEMA::Production to TilewRole
GRANT INSERT ON SCHEMA::Production to TilewRole
-- #### schema HumanResources ####
GRANT SELECT ON SCHEMA::HumanResources to TilewRole
--GRANT UPDATE ON SCHEMA::HumanResources to TilewRole
--GRANT DELETE ON SCHEMA::HumanResources to TilewRole
--GRANT INSERT ON SCHEMA::HumanResources to TilewRole
-- #### schema Person ####
GRANT SELECT ON SCHEMA::Person to TilewRole
--GRANT UPDATE ON SCHEMA::Person to TilewRole
--GRANT DELETE ON SCHEMA::Person to TilewRole
GRANT INSERT ON SCHEMA::Person to TilewRole
-- #### schema etc ... ####
Once again, thank you. I really appreciate the efforts and the new information you gave me
January 19, 2012 at 7:00 am
Don't know if this will help, but see this link for a script I wrote for creating an instance audit documentation script. If you set the @outputtype variable = 2 (=1 just does nice column printing), it will create the actual assignment statements for all objects. Just cut and past for the database (as is, does all databases) and for the role you want, and do a search/replace from the old role name to the new role name. Then you will have the permission statements for the new role. Hope this helps.
http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx#bm1230153
January 19, 2012 at 5:38 pm
vikingDBA (1/19/2012)
Don't know if this will help, but see this link for a script I wrote ... Hope this helps.http://www.sqlservercentral.com/Forums/Topic1226870-359-1.aspx#bm1230153
Neet and huge program! I hope I'll be able to do that one day! :w00t:
But does not do what I want (well... I think it does not) as it gives me the permission ON the role and who is IN the role, but not the permission TO the schemas (and/or table).
RoleName UserName UserType
========================= ========================= =========================
TilewRole --none--
StateDesc PermName Schema Object User ObjectType UserType
=============== =============== ========== ======================== ===============
GRANT DELETE sys sysallocunits TilewRole SYSTEM_TABLE DATABASE_ROLE
GRANT INSERT sys sysallocunits TilewRole SYSTEM_TABLE DATABASE_ROLE
GRANT SELECT sys sysallocunits TilewRole SYSTEM_TABLE DATABASE_ROLE
GRANT UPDATE sys sysallocunits TilewRole SYSTEM_TABLE DATABASE_ROLE
GRANT SELECT sys sysrowsets TilewRole SYSTEM_TABLE DATABASE_ROLE
From my previous example (my Query file), I want a way that it could give me that TilewRole IS:
GRANT SELECT ON SCHEMA Production
GRANT UPDATE ON SCHEMA Production
GRANT DELETE ON SCHEMA Production
GRANT INSERT ON SCHEMA Production
GRANT SELECT ON SCHEMA HumanResources
GRANT SELECT ON SCHEMA Person
GRANT INSERT ON SCHEMA Person
Maybe I do not give the good explanation on what I want or maybe its is so special what I want that the commands to be able to do it do not exist in SQL 2008...
Anyway, like I said, I found a way to do it with "my Query file": 100 rows to comments or un-comments dependant of the role I want to create for specific accessibility on the database, but it worked fine and it was relatively efficient the way I did it.
Thanks for your code. Never has too much to learn!
January 20, 2012 at 6:18 am
You are wanting to do schema-wide permissions? If that is true, it is not to the best practice of "least-permissions", only giving them permissions on the objects they need. If you give schema-wide permissions, it is not granular, therefore not to the best practice. It is your choice, of course.
Please clarify.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply