SQL Server : Server-Level Security

When created via the GUI, you might find it hard to distinguish between a login and user.

  • Logins : Allow connections to the server and can have server permissions assigned to them.
  • Users  : Determine which databases regular users have access to, as well as database and object level privileges granted to them.

Logins

Here are some examples of creating logins.

-- AD User
CREATE LOGIN [domain\username] FROM WINDOWS

-- AD Group
CREATE LOGIN [domain\groupname] FROM WINDOWS

-- SQL Server user.
CREATE LOGIN MyUser WITH PASSWORD=N'MyPassword1' MUST_CHANGE,
 DEFAULT_DATABASE=MyDatabase,
 CHECK_EXPIRATION=ON,
 CHECK_POLICY=ON

For more information see:

Roles

Server Roles:

The sysadmin role is a super user. Be careful who you grant it to.

Check the components of a server role using the following command.

sp_srvrolepermission 'public'

Add a person to a server role.

ALTER SERVER ROLE sysadmin
ADD MEMBER [domain\username]
GO

Create a new server role and assign it to a user.

CREATE SERVER ROLE [MyRole]

ALTER SERVER ROLE [MyRole]
ADD MEMBER [domain\username]
GO

For more information see:

Granting Server-Level Permissions

View available server properties.

  • Right-click on the database server and select the “Properties” menu option.
  • Click on the permissions tab.
  • The list at the bottom shows the available server permissions.

These permissions can be granted to roles or logins.

  • Click on the Role or Login.
  • Check the permissions required.
  • Click the OK button.

The grant option allows the grantee to grant the role to others.

Here’s how you would do it from T-SQL for the “VIEW SERVER STATE” property.

USE master
GO

GRANT VIEW SERVER STATE TO [MyRole]
GO

GRANT VIEW SERVER STATE TO [domain\username]
GO

GRANT VIEW SERVER STATE TO [MyRole] WITH GRANT OPTION AS [sa]
GO

For more information see: