February 25, 2014 at 4:58 pm
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
February 25, 2014 at 5:04 pm
In SSMS:
Tools -> Options -> SQL Server object explorer -> Scripting: in Object scripting options check the Script permissions.
Igor Micev,My blog: www.igormicev.com
February 25, 2014 at 5:40 pm
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.
February 26, 2014 at 2:56 pm
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.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
February 26, 2014 at 3:14 pm
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply