Getting started with Microsoft SQL Server OVER Clause PIVOT / UNPIVOT Database Snapshots Retrieve information about the database The STUFF Function FOR XML PATH Cursors Join Common Table Expressions Move and copy data around tables Dates Limit Result Set Retrieve Information about your Instance With Ties Option Variables JSON in Sql Server Window functions Partitioning Stored Procedures GROUP BY Generating a range of dates COALESCE Split String function in Sql Server INSERT INTO CREATE VIEW String Functions Resource Governor ORDER BY WHILE loop System database - TempDb Migration Primary Keys MERGE Full-Text Indexing FOR JSON SELECT statement DBMAIL Index Queries with JSON data Storing JSON in SQL tables OPENJSON Ranking Functions Trigger Converting data types NULLs Transaction isolation levels Advanced options IF...ELSE TRY/CATCH Data Types User Defined Table Types Table Valued Parameters In-Memory OLTP (Hekaton)Temporal Tables Insert Sequences SCOPE_IDENTITY()Views Use of TEMP Table Scheduled Task or Job Isolation levels and locking Sorting/ordering rows Privileges or Permissions Foreign Keys SQLCMD File Group cross apply Basic DDL Operations in MS SQL Server Computed Columns UNION Subqueries Last Inserted Identity CLUSTERED COLUMNSTORE Parsename Installing SQL Server on Windows Aggregate Functions Querying results by page Schemas Backup and Restore Database Transaction handling Natively compiled modules (Hekaton)Database permissions Spatial Data Dynamic SQL Pagination Query Hints Modify JSON text Row-level security Dynamic data masking Export data in txt file by using SQLCMD Encryption Managing Azure SQL Database Common Language Runtime Integration Delimiting special characters and reserved words CASE Statement DBCC BULK Import Query Store Service broker Analyzing a Query Microsoft SQL Server Management Studio Shortcut Keys Permissions and Security PHANTOM read Filestream Drop Keyword String Aggregate functions in SQL Server SQL Server Evolution through different versions (2000 - 2016)SQL Server Management Studio (SSMS)Logical Functions Dynamic SQL Pivot Alias Names in Sql Server bcp (bulk copy program) Utility

Database permissions

Remarks:

Basic Syntax:

{GRANT| REVOKE | DENY} {PERMISSION_NAME} [ON {SECURABLE}] TO {PRINCIPAL};

{GRANT| REVOKE | DENY} - What you're trying to accomplish
- Grant: "Give this permission to the stated principal"
- Revoke: "Take this permission away from the stated principal"
- Deny: "Make sure the stated principal never has this permission (i.e. "DENY SELECT" means that regardless of any other permissions, SELECT will fail for this principal)
PERMISSION_NAME - The operation that you're attempting to affect. This will depend on the securable. For instance, it doesn't make sense to GRANT SELECT on a stored procedure.
SECURABLE - The name of the thing on which you're trying to affect permissions on. This is optional. Saying GRANT SELECT TO [aUser]; is perfectly acceptable; it means "for any securable for which the SELECT permission makes sense, GRANT that permission".
PRINCIPAL - For whom you are trying to affect permissions. At a database level, this can be a role (application or database) or user (mapped to a login or not) for example.

Changing permissions

GRANT SELECT ON [dbo].[someTable] TO [aUser];

REVOKE SELECT ON [dbo].[someTable] TO [aUser];
--REVOKE SELECT [dbo].[someTable] FROM [aUser]; is equivalent

DENY SELECT ON [dbo].[someTable] TO [aUser];

CREATE USER

--implicitly map this user to a login of the same name as the user
CREATE USER [aUser];

--explicitly mapping what login the user should be associated with
CREATE USER [aUser] FOR LOGIN [aUser];

CREATE ROLE

CREATE ROLE [myRole];

Changing role membership

-- SQL 2005+
exec sp_addrolemember @rolename = 'myRole', @membername = 'aUser';
exec sp_droprolemember @rolename = 'myRole', @membername = 'aUser';

-- SQL 2008+
ALTER ROLE [myRole] ADD MEMBER [aUser];
ALTER ROLE [myRole] DROP MEMBER [aUser];

Note: role members can be any database-level principal. That is, you can add a role as a member in another role. Also, adding/dropping role members is idempotent. That is, attempting to add/drop will result in their presence/absence (respectively) in the role regardless of the current state of their role membership.

Contributors

Topic Id: 6788

Example Ids: 23057,23058,23059,23060

This site is not affiliated with any of the contributors.