SQL Server Security

1.Logins & Server Roles

  • Logins and Server Roles define users and permissions at the Instance level.
  • Server Role permissions cannot be seen through the user interface, to do this you need to execute:
sp_srvrolepermission
  • If you want to grant or deny a specific permission on a database to a user you can use the following command:
USE [database]
GO
DENY ALTER TO [domain\user]
GO

NOTE: Deleting the Logins won’t delete the Users at the database level. This will result in orphaned Users.

2.Users & Database Roles

  • Users and Database Roles define users and permissions at the Database level.
  • The User name can be different from the associated Login name, but this is not recommended.
  • In case you have an orphaned user you can reassign the User to a Login using the command:
ALTER USER domain\user WITH LOGIN = domain\user
GO
  • If you want to give a User specific permissions on tables, databases, etc. you can do this with “Securables” (see the image below).

  • You can use Schemas to group objects(tables, procs, views, etc.) and apply user permissions on that schema. To apply permissions use the following command:
GRANT SELECT ON SCHEMA::[dbo] TO [domain\user]

3.Connections

  • In case you only want to permit remote access from a specific server and user, you can do this through Firewall Rules. Create a rule for each server that needs to connect to the SQL Server and specify “Remote Computers” and “Remote Users” for that each rule.
  • You can also control acces to SQL through “Endpoints”. You can use endpoints to enable SQL connections on multiple TCP ports. Endpoint can be configured only through Transact-SQL.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s