Question:
When performing a Complete Compare on a new model I noticed that the 'deny' permission is not reflected in the Alter script. How do I generate an Alter Script with Complete Compare to have the permission: 'deny' or 'grant' on a specific table?
Answer:
The following are steps to add permissions to users on specific tables:
Create a new model with one Entity/Table
1. Right click table to get Table Properties and then go to Permission tab.
Assign the permission for this table and click the button in the red box to get to the Permission Editor:
2. Check the permission you need, in the following example, the box "Select" was checked meaning the table's select access has been set to DENY for owner "bob":
3. A Complete Compare with a blank model can then be generate to see if the DENY syntax will be generated in the Alter script:
4. Import the permission to the blank model by clicking the right arrow at table level:
5. Click the Right Alter Script/Schema Generation button, check the box "Permission"
6. Click the OK button to generate the Deny syntax like in Alter script below:
CREATE TABLE [Test_Tab1]
(
[a] char(18) NOT NULL ,
[a1] char(18) NOT NULL
)
go
ALTER TABLE [Test_Tab1]
ADD CONSTRAINT [XPKTest_Tab1] PRIMARY KEY CLUSTERED ([a] ASC)
go
DENY SELECT
ON OBJECT :: [Test_Tab1]
TO bob
go
