Database Users
Users are based on Logins (AD user, AD group, SQL Server).
CREATE USER [domain\username] FOR LOGIN [domain\username] WITH DEFAULT SCHEMA=[dbo] GO
For more information see:
To run as a user. (switch context).
EXECUTE AS USER = '[domain\username]' -- Do something. -- Switch back to normal context. REVERT
Grant privileges to a user.
GRANT EXECUTE TO [domain\username] GRANT EXECUTE ON object-name TO [domain\username] GRANT SELECT, INSERT, UPDATE, DELETE ON schema.table-name TO [domain\username]
It’s better to use a database role, so the same privileges can easily be granted to others.
CREATE ROLE [MyRole] GRANT SELECT, INSERT, UPDATE, DELETE ON schema.table-name TO [MyRole] ALTER SERVER ROLE [MyRole] ADD MEMBER [domain\username] GO
Schemas
A schema is a collection of objects. Here are some examples of creating a schema.
CREATE SCHEMA [MySchema] AUTHORIZATION [domain\username] CREATE SCHEMA [MySchema2]
Create and object in the schema.
CREATE TABLE MySchema.MyTable ( id int )
This gives a logical group of objects, which is good for organisation and security. Rather than granting permissions on the object, you can grant them for the schema.
GRANT SELECT, INSERT, UPDATE, DELETE ON schema::MySchema TO [MyRole]
Without a scope qualifier, the default scope is “object”. You can alter this for a statement using a scope qualifier like “schema::”.
For more information see: