Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
 Posted - 2009-09-28 : 07:01:58
|
| Does anyone know what is the name of the system sp that allows you to add a schema to a role?I know the one to add a user to a role is called sp_addrolemember but I can't find the one to add a schema to a role?Thanks |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2009-09-28 : 19:39:29
|
| Does this fit what you're looking for?ALTER AUTHORIZATION ON [ <entity_type> :: ] entity_name TO { SCHEMA OWNER | principal_name }<entity_type> ::= { Object | Type | XML Schema Collection | Fulltext Catalog | Schema | Assembly | Role | Message Type | Contract | Service | Remote Service Binding | Route | Symmetric Key | Endpoint | Certificate | Database }=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
 |
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2009-09-29 : 05:01:14
|
| On using that, doesn't it transfer ownership of the schema to the role?I want to give a role (and all users within that role) SELECT rights to a schema (and all the tables within it)Is that the same ?I was hoping I could do it programatically through a cursor I have created which iterate's through system tables (sys.database_role_members & sys.database_permissions) to get existing user rights. |
|
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2009-09-29 : 05:23:06
|
| I have tried the following but it doesn't seem to work?GRANT SELECT ON SCHEMA :: schemaname TO rolename |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2009-09-29 : 11:58:50
|
| http://msdn.microsoft.com/en-us/library/ms187940.aspxThis link looks promising...http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2009-09-30 : 02:16:27
|
| Thanks all for your help, I found the problem and developed a solution:-Background:- Users had been put into the wrong roles on the server by my predecessor (real messy - some in serveradmin and/or in db admin type roles!) - got to be cleaned up before we upgrade to new server and 2008!Problem - On running the GRANT script in my post above, it reported that it had been successfull, but when i tried viewing it to check the permissions, it reported an error (right click on the role, then choose properties, then securables - error!), hence my post above!On checking, I found the Role I created had somehow became corrupted, I dropped it and recreated it and I was then able to use the script in my above post to test i could grant permissions on it, all OK.I have now developed a script to read from the DMV's users logons,roles and rights, which i then use to populate the new roles i created. I can then script them out from the roles they are wrongly assigned to with the least amount of disruption!Job Sorted (almost - as I now have the means but need to go through all db's on the server and apply this!) |
|
|
|
|
|
|
|
|
What is the name of the system sp.......