SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Removing the GRANTOR reference


Removing the GRANTOR reference

Author
Message
andre.quitta
andre.quitta
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 405
I have a table driven script that will automatically add a role with a set of associated GRANTs. When

I create the role and later verify it's permissions, it shows the correct GRANT but with a GRANTOR of "dbo". I may be fickle about this, but I'd like to remove the reference to dbo, to reduce clutter, because now every permission is doubled: one where the Grantor is blank and the other where it's dbo.

I've tried scripting it out, but the T-SQL creates it anyway. Code outline is below:
Suggestions?


use MyDbName
go
create role dboNewRole
go
GRANT ALTER ON SCHEMA::dbo TO dboNewRole
GRANT DELETE ON SCHEMA::dbo TO dboNewRole
GRANT EXECUTE ON SCHEMA::dbo TO dboNewRole
GRANT INSERT ON SCHEMA::dbo TO dboNewRole
GRANT REFERENCES ON SCHEMA::dbo TO dboNewRole
GRANT SELECT ON SCHEMA::dbo TO dboNewRole
GRANT UPDATE ON SCHEMA::dbo TO dboNewRole
GO

Igor Micev
Igor Micev
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19652 Visits: 5191
In SSMS:
Tools -> Options -> SQL Server object explorer -> Scripting: in Object scripting options check the Script permissions.

Igor Micev,
My blog: www.igormicev.com
andre.quitta
andre.quitta
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 405
Currently, these are the settings:


Script owner False
Script permissions False



When I changed them to True, there was no difference when I re-ran the code.
Erland Sommarskog
Erland Sommarskog
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13210 Visits: 879
I found https://connect.microsoft.com/SQLServer/feedback/details/797967/ssms-create-script-duplicates-the-statements-for-grant-or-deny-column-permissions and the response from Microsoft is that this has been fixed. I have not made research to see if the fix is included in any of the most recent cumulative updates to SQL 2012.

Note that if you install the update, it's important that you apply on the machine where you run SSMS, because this is a bug in SSMS, not the engine itself.

Erland Sommarskog, SQL Server MVP, www.sommarskog.se
andre.quitta
andre.quitta
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1025 Visits: 405
Thanks. We have SQL 2012 sp1 CU7, dated November 13, 2013

The link you provided says it was fixed in CU8, dated January 20, 2014. Will check it out.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search