Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Removing the GRANTOR reference Expand / Collapse
Author
Message
Posted Tuesday, February 25, 2014 4:58 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 10:58 AM
Points: 98, Visits: 267
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
Post #1545169
Posted Tuesday, February 25, 2014 5:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:19 AM
Points: 2,963, Visits: 2,982
In SSMS:
Tools -> Options -> SQL Server object explorer -> Scripting: in Object scripting options check the Script permissions.




Igor Micev,
SQL Server developer at Seavus
www.seavus.com
Post #1545171
Posted Tuesday, February 25, 2014 5:40 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 10:58 AM
Points: 98, Visits: 267
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.
Post #1545186
Posted Wednesday, February 26, 2014 2:56 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:07 AM
Points: 803, Visits: 720
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
Post #1545604
Posted Wednesday, February 26, 2014 3:14 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 4, 2014 10:58 AM
Points: 98, Visits: 267
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.
Post #1545611
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse