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
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 393
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
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

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

Igor Micev,
SQL Server developer at Seavus
My blog: www.igormicev.com
andre.quitta
andre.quitta
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 393
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2113 Visits: 872
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
SSC-Enthusiastic
SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)SSC-Enthusiastic (197 reputation)

Group: General Forum Members
Points: 197 Visits: 393
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