Sql Server allows assigning or GRANTing different applicable permissions to different objects. Since there are so many nuances, sometimes it gets confusing. So this cheat sheet helps reduce the confusion and always come in handy when assigning permissions to securables. Some are listed below:
- Tables
- View
- Type
- Stored Procedure
- Functions
- Synonym
- User
- Role
- Schema
- Database
- Login
- Server
Tables:
- ALL does not mean all possible permissions. For tables it only means DELETE, INSERT, REFERENCES, SELECT, UPDATE.
- Using the phrase ‘OBJECT’ is optional, but a good practice to follow – it helps with other objects.
--
-- Grant permissions to tables
--
GRANT SELECT ON OBJECT::dbo.StoredValue TO SomeUser
GRANT SELECT, UPDATE, DELETE ON OBJECT::dbo.StoredValue TO SomeUser
GRANT ALL ON OBJECT::dbo.StoredValue TO SomeUser
GO
Views:
- ALL does not mean all possible permissions. For tables it only means DELETE, INSERT, REFERENCES, SELECT, UPDATE.
- Using the phrase ‘OBJECT’ is optional, but a good practice to follow – it helps with other objects.
--
-- Grant permissions to views
--
GRANT SELECT ON OBJECT::dbo.vw_StoredValue TO SomeUser
GRANT SELECT, UPDATE, DELETE ON OBJECT::dbo.vw_StoredValue TO SomeUser
GRANT ALL ON OBJECT::dbo.vw_StoredValue TO SomeUser
GO
Type:
--
-- Grant permissions to views
--
GRANT VIEW DEFINITION ON TYPE::dbo.SSN TO SomeUser
GO
Stored Procedures:
- ALL does not mean all possible permissions. For stored procedures it only means EXECUTE.
- Using the phrase ‘OBJECT’ is optional, but a good practice to follow – it helps with other objects.
--
-- Grant permissions to views
--
GRANT EXECUTE ON OBJECT::dbo.usp_Expired_StoredValue TO SomeUser
GRANT ALL ON OBJECT::dbo.usp_Expired_StoredValue TO SomeUser
GO
Functions:
- Assigning ALL permissions only means the following for functions
- Scalar function permissions: EXECUTE, REFERENCES.
- Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.
--
-- Grant permissions on functions
--
GRANT EXECUTE ON OBJECT::dbo.udf_WhatIsMyAge TO SomeUser
GRANT ALL ON OBJECT::dbo.udf_CalculateAge TO SomeUser
GO
Synonym:
- When dealing with Synonyms we still need to follow the same ownership-chain. Along with permissions to use synonym, we also need permissions base objects.
--
-- Grant permissions on Synonyms
--
GRANT EXECUTE ON OBJECT::dbo.Syn_sp_TestProc TO SomeUser
DENY EXECUTE ON OBJECT::dbo.Syn_sp_TestProc TO SomeUser
GO
User:
- A user is a database-level securable contained by the database in which it exists.
--
-- Grant permissions on Users
--
GRANT CONTROL ON USER::SomeUser1 TO OtherUser
GRANT IMPERSONATE ON USER::SomeUser1 TO OtherUser
</pre><pre>GO
Role:
- Just like user, a Role is also a database-level securable, in which it exists.
--
-- Grant permissions on Role
--
GRANT ALTER ON ROLE::r_ExecuteReportsOnWeekdays TO SomeUser
GO
Schema:
- A schema is also a database-level securable, in which it exists.
--
-- Grant permissions on schema
--
GRANT SELECT ON SCHEMA::HumanResource TO SomeUser
GRANT INSERT ON SCHEMA::ArchiveObjects TO SomeUser
GO
Database:
- A database is a instance-level securable, in which it exists.
- Several permissions could be assigned on a database; When we mention ALL, the following specific permissions are assigned implicitly:
- BACKUP DATABASE
- BACKUP LOG
- CREATE DATABASE
- CREATE DEFAULT
- CREATE FUNCTION
- CREATE PROCEDURE
- CREATE RULE
- CREATE TABLE
- CREATE VIEW
- SHOWPLAN
--
-- Grant permissions on database
--
USE [SomeDatabase]
GO
GRANT CREATE TABLE TO SomeUser
GO
--
-- Grant permissions on see execution plan to a login
--
USE [TestDB]
GO
GRANT SNOWPLAN ON DATABASE::TestDB TO [SomeUser]
GO
Login:
- Logins are also instance-level securables.
--
-- Grant permissions on Logins
--
GRANT IMPERSONATE ON LOGIN::ReportsLogin TO AdhocReports_Login
GO
Server:
- Permissions at server scope could only be assigned when you are pointed to master database.
--
-- Grant permissions on server level objects
--
GRANT ALTER ANY DATABASE TO DBA_Group
GRANT SERVER CONTROL TO DBA_Group
GO
Hope this helps,
_Sqltimes
Read Full Post »
Playing with database servers... 