EXEC sp_addrolemember n ' db_owner ', n ' db '----set DB as a member of the db_owner role
EXEC sp_droprolemember n ' db_owner ', n ' db '----remove db from db_owner role
ALTER AUTHORIZATION on Schema::[db_datareader] to DBS----gives DB the owning db_datareader architecture
Alter AUTHORIZATION on Schema::[db_datareader] to db---Modify the owner of the db_datareader schema to db
ALTER AUTHORIZATION on schema::[db_ddladmin] to [db_datareader]---Modify the schema owned by the db_datareader role
---granted XX permission
GRANT VIEW DEFINITION on [dbo]. [Psyp_databasebackup] to [db]--view
GRANT ALTER on [dbo]. [Psyp_databasebackup] to [db]--Modify
GRANT take OWNERSHIP on [dbo]. [Psyp_databasebackup] to [db]--Take over ownership
GRANT CONTROL on [dbo]. [Psyp_databasebackup] to [DB]-control rights
GRANT EXECUTE on dbo. [Psyp_databasebackup] To DB--execution
---endowed with the privilege of granting XX
---if you give permission directly, you give XX permission by default.
GRANT VIEW DEFINITION on [dbo]. [Psyp_databasebackup] to [DB] with Grant OPTION-----View permissions and have grant permissions
GRANT ALTER on [dbo]. [Psyp_databasebackup] to [db] with GRANT OPTION
GRANT take OWNERSHIP on [dbo]. [Psyp_databasebackup] to [db] with GRANT OPTION
GRANT CONTROL on [dbo]. [Psyp_databasebackup] to [db] with GRANT OPTION
GRANT EXECUTE on [dbo]. [Psyp_databasebackup] to [db] with GRANT OPTION
----Deny XX permission
DENY VIEW DEFINITION on [dbo]. [Psyp_databasebackup] to [db] CASCADE
DENY ALTER on [dbo]. [Psyp_databasebackup] to [db] CASCADE
DENY take OWNERSHIP on [dbo]. [Psyp_databasebackup] to [db] CASCADE
DENY CONTROL on [dbo]. [Psyp_databasebackup] to [db] CASCADE
DENY EXECUTE on [dbo]. [Psyp_databasebackup] to [db] CASCADE
---recover XX permissions
REVOKE VIEW DEFINITION on [dbo]. [Psyp_databasebackup] to [db]---view definition
REVOKE ALTER on [dbo]. [Psyp_databasebackup] to [db]--Modify
REVOKE take OWNERSHIP on [dbo]. [Psyp_databasebackup] to [db]--Take over ownership
REVOKE CONTROL on [dbo]. [Psyp_databasebackup] to [DB]-control rights
REVOKE EXECUTE on dbo. [Psyp_databasebackup] To DB--execution
---reclaim xx with grant permission
REVOKE GRANT OPTION for VIEW DEFINITION on [dbo]. [Psyp_databasebackup] to [db] CASCADE
REVOKE GRANT OPTION for ALTER on [dbo]. [Psyp_databasebackup] to [db] CASCADE
REVOKE GRANT OPTION for Take OWNERSHIP on [dbo]. [Psyp_databasebackup] to [db] CASCADE
REVOKE GRANT OPTION for CONTROL on [dbo]. [Psyp_databasebackup] to [db] CASCADE
REVOKE GRANT OPTION for EXECUTE on [dbo]. [Psyp_databasebackup] to [db] CASCADE
--------assigning permissions to a table
---endowed with the privilege of granting XX
GRANT INSERT on [dbo]. [A] to [db] with GRANT OPTION
GRANT VIEW DEFINITION on [dbo]. [A] to [db] with GRANT OPTION
GRANT VIEW change TRACKING on [dbo]. [A] to [db] with GRANT OPTION
GRANT ALTER on [dbo]. [A] to [db] with GRANT OPTION
GRANT UPDATE on [dbo]. [A] to [db] with GRANT OPTION
GRANT take OWNERSHIP on [dbo]. [A] to [db] with GRANT OPTION
GRANT CONTROL on [dbo]. [A] to [db] with GRANT OPTION
GRANT DELETE on [dbo]. [A] to [db] with GRANT OPTION
GRANT SELECT on [dbo]. [A] to [db] with GRANT OPTION
GRANT REFERENCES on [dbo]. [A] to [db] with GRANT OPTION
----------tables and views have the same permissions
Insert--Insertion
View definition--viewing definitions
View change TRACKING--Viewing changes tracking
Alter---Modify
Update---Updates
Take OWNERSHIP---takeover ownership
Control---Controls
Delete---Remove
Select---Selection
REFERENCES--References
----The permissions that a stored procedure has
View DEFINITION---Viewing
Alter---Modify
Take OWNERSHIP---takeover ownership
Control---Controls
Execute---Execution
-----Permissions for scalar functions
View definition---Viewing definitions
Alter---Modify
Take OWNERSHIP---takeover ownership
Control---Controls
REFERENCES---References
Execute---Execution
Permissions------Table-valued functions
View definition---Viewing definitions
Alter---Modify
Take OWNERSHIP--taking over ownership
Control---Controls
REFERENCES---References
Select---Selection
MS SQL Permissions Settings script