Duluth, GA
+ 678-753-4494
contact@datahills.us

Sql Server : GRANT or Assign permissions on table, procedure, function, type, etc to user accounts

Sql Server : GRANT or Assign permissions on table, procedure, function, type, etc to user accounts

  • Posted by Sqltimes
  • On December 19, 2015
  • 0 Comments

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.
1
2
3
4
5
6
7
--
-- 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.
1
2
3
4
5
6
7
--
-- 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:

1
2
3
4
5
--
-- 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.
1
2
3
4
5
6
--
-- 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.
1
2
3
4
5
6
--
-- 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.
1
2
3
4
5
6
--
-- 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.
1
2
3
4
5
6
--
-- 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.
1
2
3
4
5
--
-- 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.
1
2
3
4
5
6
--
-- 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
--
-- 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.
1
2
3
4
5
--
-- 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.
1
2
3
4
5
6
--
-- Grant permissions on server level objects
--
GRANT ALTER ANY DATABASE      TO DBA_Group
GRANT SERVER CONTROL          TO DBA_Group
GO

 

Hope this helps,
_Sqltimes

 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *