security

Row-level security in SQL Server 2016

RLS: Row-level security = restrict access to individual rows in a table.
- Allows you to write custom logic to control user access to every row.
- Security predicate function = ordinary inline schema-bound table-valued function whose parameters are mapped to column values.

CREATE SECURITY POLICY sec.MyTablePolicyFilter
    ADD FILTER PREDICATE sec.fn_mySecurityPredicate(MyColumnName)
    ON dbo.MyTable
    WITH (STATE = ON)

How to grant sysadmin role to a user

Use sp_addsrvrolemember to add a login as a member of a fixed server role.

-- Create login first
USE [master]
CREATE LOGIN <login_name> WITH PASSWORD = '<YourPassword>', DEFAULT_DATABASE=[master];

-- Or create login from a Windows domain account
CREATE LOGIN [MyDomain\MyUser] FROM WINDOWS;
USE [master]

-- Grant role to newly created login
EXEC master..sp_addsrvrolemember @loginame = N'MyDomain\MyUser', @rolename = N'sysadmin'

Subscribe to RSS - security