SQL Server Instance Security !

Security in sql server 2008 can be thought of from 2 point of views :

Instance-wide security and
Database-wide security

Lets now talk about instance-wide security.
Goto a particular Instance expand the security node and it contains
logins
server roles
credentials
cryptographic providers
audits
server audit specifications
Lets discuss about each of them in detail :

Logins :
A login is a principal in sql server terminology which identifies itself with sql sever as an entity different from others.

Logins indicate authentication and giving permissions to logins indicate authorization.

A login can be either windows login or sql server login.

creating a windows login :

CREATE LOGIN [HOME-BIX5T88DR6\kalyan] FROM WINDOWS WITH DEFAULT_DATABASE=[destiny], DEFAULT_LANGUAGE=[us_english]
GO


creating a sql server login :

CREATE LOGIN [sqlcat2] WITH PASSWORD=N'xxxxxx', DEFAULT_DATABASE=[destiny], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Another example :
USE [master]
GO
CREATE LOGIN [sqlcat3] WITH PASSWORD=N'sqlcat3' MUST_CHANGE, DEFAULT_DATABASE=[destiny], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
EXEC master..sp_addsrvrolemember @loginame = N'sqlcat3', @rolename = N'dbcreator'
GO

Dropping a login :
IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'sqlcat3')
DROP LOGIN [sqlcat3]
GO

Altering a login :
USE [master]
GO
ALTER LOGIN [sqlcat3] WITH DEFAULT_DATABASE=[destiny], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Before we talk about logins in detail lets see what are the server roles available ?

Server Roles :

There are 9 server roles available.

Ideally in windows,instead of giving permissions to each and every user what we do is we create a group,add members to that group and then set permissions to that group.Apparently all members of that group will have access levels and roles played as per the group.

Similarly server roles can be thought of as groups in windows.We have 9 server roles.We can add logins to the server roles and they would eventually inherit the capabilities of the server role assigned to them.

sysadmin System Administrators
securityadmin Security Administrators
serveradmin Server Administrators
setupadmin Setup Administrators
processadmin Process Administrators
diskadmin Disk Administrators
dbcreator Database Creators
bulkadmin Bulk Insert Administrators
public

Lets inspect each of these roles in detail :

sysadmin-members of this role can do anything across the server.
securityadmin-members of this role can add logins,delete,grant permissions to logins,deny,revoke etc
serveradmin-members of this role can do server-wide configurations and can also shut down sql server
setupadmin-members of this role can setup things like installations,linked servers installation etc
processadmin-members of this role can start and end processes within sql server
diskadmin-members of this role can manage disk files,shrinking them,backing them etc etc
dbcreator-members of this role can create,delete databases etc
bulkadmin-members of this role can perform bulk inserts
public-Every SQL Server login belongs to the public server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on any object when you want the object to be available to all users.

Security related Stored Procedures are as follows :

Sp_addlogin – to add sql server login
Sp_grantlogin-to add windows nt login
Sp_helplogins-to view all logins
Sp_droplogin-to drop an existing login
Sp_denylogin-deny a login from connecting to an instance of sql server
Sp_revokelogin-revokes a login.

Instead of these system stored procedures it is advisable to use the below syntaxes to create logins,alter and drop.
Create login
Alter login
Drop login

Q) How to find the local server name,the remote servers configured to our local server and also replication servers if configured ?
A) exec sp_helpserver

Q) How to add remote server to our local server instance,configure it for data access ?
A) exec sp_addserver 'HOME-BIX5T88DR6\PRODSERVER2008'
once u execute this, goto Server Objects node expand Linked Servers and u shud find the above server instance visible there.
now try to execute the following :
select * from [HOME-BIX5T88DR6\PRODSERVER2008].yogesh.dbo.yog

you will get an error message saying that this instance is not configured for data access.This is because we have just added the server and not configured it for data access.Now we execute the below query to configure it for data access :
exec sp_serveroption @server='HOME-BIX5T88DR6\PRODSERVER2008', @optname='data access',@optvalue='true'
now re-rerun the query
select * from [HOME-BIX5T88DR6\PRODSERVER2008].yogesh.dbo.yog
now u should be able to see the records.

Q) How to drop remote server to our local server instance,disable it for data access ?
A) exec sp_dropserver 'HOME-BIX5T88DR6\PRODSERVER2008'
exec sp_serveroption @server='HOME-BIX5T88DR6\PRODSERVER2008', @optname='data access',@optvalue='false'


Q) What is the stored procedure to add a new remote login ID on the local server ?
A) sp_addremotelogin @remoteserver = 'remoteserver' ,
@loginame = 'local_login_name' ,
@remotename = 'remote_login_name'

Q) What is the stored procedure to drop a new remote login ID on the local server ?
A) exec sp_dropremotelogin @remoteserver = 'HOME-BIX5T88DR6\PRODSERVER2008' ,
@loginame = 'sqlcat3' ,
@remotename = 'caspro'

Q) What is the stored procedure to view existing remote login ID on the local server ?
A) EXEC sp_helpremotelogin

Q) There are remote logins being mapped to our local logins and a remote server is configured for data access within our local server.Can we drop the remote server ?

A) No.We cannot drop the remote server using sp_dropserver until unless we drop the remote login id's that are mapped to out local logins.

Q) Whats the other way of configuring linked server ( hint -via GUI).Explain.

A)
Create a txt file on your system and rename it with ext udl.
Now double-click the file and choose the server name,database etc.Test the connectivity and make sure the test connection is successful.Click ok.Now open the same udl doc with textpad.keep this notepad open.
Now goto server objects node,expand linked servers,rt-click and say new linked server,supply the information from the opened textpad in the above step.Remember the product name is the provider for example : SQLNCLI10.say ok.The linked server is now ready and you can query data out of your linked server.

Code is pasted below :

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver @server = N'LS_SERVER_2008', @srvproduct=N'SQLNCLI10', @provider=N'SQLNCLI10', @datasrc=N'HOME-BIX5T88DR6\PRODSERVER2008', @provstr=N'Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=yogesh;Data Source=HOME-BIX5T88DR6\PRODSERVER2008;Initial File Name="";Server SPN=""', @catalog=N'yogesh'

GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LS_SERVER_2008', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LS_SERVER_2008', @locallogin = N'sqlcat3', @useself = N'False', @rmtuser = N'caspro_user1', @rmtpassword = N'caspro'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LS_SERVER_2008', @locallogin = NULL , @useself = N'True'
GO

Now query data out of tables as follows :

select * from LS_SERVER_2008.yogesh.dbo.yog

Q) Give the code for dropping an existing linked server.
A)
/****** Object: LinkedServer [LS_SERVER_2008] Script Date: 01/31/2010 15:59:20 ******/
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'LS_SERVER_2008')EXEC master.dbo.sp_dropserver @server=N'LS_SERVER_2008', @droplogins='droplogins'
GO

Q) How to Create or update a mapping between a login on the local instance of SQL Server and a security account on a remote server ?
A)
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | 'NULL']
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]

Arguments :-)

[ @rmtsrvname = ] 'rmtsrvname'

Is the name of a linked server that the login mapping applies to. rmtsrvname is sysname, with no default.

[ @useself = ] 'TRUE' | 'FALSE' | 'NULL'

Determines whether to connect to rmtsrvname by impersonating local logins or explicitly submitting a login and password. The data type is varchar(8), with a default of TRUE.

A value of TRUE specifies that logins use their own credentials to connect to rmtsrvname, with the rmtuser and rmtpassword arguments being ignored. FALSE specifies that the rmtuser and rmtpassword arguments are used to connect to rmtsrvname for the specified locallogin. If rmtuser and rmtpassword are also set to NULL, no login or password is used to connect to the linked server.

[ @locallogin = ] 'locallogin'

Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.

[ @rmtuser = ] 'rmtuser'

Is the remote login used to connect to rmtsrvname when @useself is FALSE. When the remote server is an instance of SQL Server that does not use Windows Authentication, rmtuser is a SQL Server login. rmtuser is sysname, with a default of NULL.

[ @rmtpassword = ] 'rmtpassword'

Is the password associated with rmtuser. rmtpassword is sysname, with a default of NULL.

Examples :
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LS_SERVER_2008', @locallogin = N'sqlcat3', @useself = N'False', @rmtuser = N'caspro_user1', @rmtpassword = N'caspro'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'LS_SERVER_2008', @locallogin = NULL , @useself = N'True'
GO

Q) How to find information about login mappings defined against a specific linked server used for distributed queries and remote stored procedures ?

A) exec sp_helplinkedsrvlogin

Q) How to Remove an existing mapping between a login on the local server running SQL Server and a login on the linked server ?

A) exec sp_droplinkedsrvlogin @rmtsrvname = 'LS_SERVER_2008' ,
@locallogin = 'sqlcat3'

Q) How to add a login as a member of a fixed server role ?
A)
sp_addsrvrolemember @loginame= 'login', @rolename = 'role'
Here role can be any of 9 fixed server roles as discussed in the beginning of this article.

Q) How to Remove a SQL Server login or a Windows user or group from a fixed server role ?
A)
sp_dropsrvrolemember @loginame = 'login' , @rolename = 'role'
Here role can be any of 9 fixed server roles as discussed in the beginning of this article.

Q) How to find the list of the SQL Server fixed server roles ?
A)
exec sp_helpsrvrole

Q) How to find information about Windows users and groups/logins that are mapped to SQL Server principals but no longer exist in the Windows environment ?
A)
EXEC sp_validatelogins

Q)By default there are some already existing logins in an instance of fresh sql server.what are they.Name few of them.

A)
BUILTIN\Administrators
NT Authority\System
NT Authority\Network Service
sa
HOME-BIX5T88DR6\SQLServer2005MSSQLUser$HOME-BIX5T88DR6$CSFSPD1CASPRO-->Instance specific and may change accordingly.This is a windows group
HOME-BIX5T88DR6\SQLServer2005SQLAgentUser$HOME-BIX5T88DR6$CSFSPD1CASPRO-->Instance specific and may change accordingly.This is a windows group
HOME-BIX5T88DR6\sqlserveragent-->this is the windows security account with which the instance of caspro server sql server agent is run.
HOME-BIX5T88DR6\sqlserverdbengine--this is the windows security account with which the instance of caspro server db engine is run.

Q) What is a credential ?
A)
A credential is a record containing the authentication information needed to connect to a resource outside of SQL Server. Most credentials consist of a Windows login name and password. On Microsoft Windows Server 2003 and later, the password may not be required.

A single credential can be mapped to multiple SQL Server logins. But a SQL Server login can be mapped to only one credential.

Only users with ALTER ANY CREDENTIAL permission can create or modify a credential.

Q)Give the code for creating a credential.
A)
USE [master]
GO
CREATE CREDENTIAL [su_creden] WITH IDENTITY = N'HOME-BIX5T88DR6\secret_user', SECRET = N'annailakshmi'
GO
Here the password annailakshmi is the password of the windows user account HOME-BIX5T88DR6\secret_user.