CreatingSqlserverwindowsandCredentials

/*

Sql server Security and Management for DBA

Create By HariPrasad Ere

*/

--To view information about logins

/*syntax sp_helplogins <loginname>

this will output loginame,security indetifier(SID),default database and language

*/

sp_helplogins 'sa'

--Returns one row for every server principal that is part of the login token.

USE master
select * from sys.login_token

/*It's time to create login */

/*

create login <login_name> with password .... for compelte synatx please refere msdn books



*/

--Ex 1 the following example will create loginname HariJamaica and with password MrPolo

create login HariJamaica with password = 'MrPolo'

-- Ex 2 the following example will create loginname for windows authentication.

-- the windows should be exist in the active directory.

create login [Hariere-PC\HariSqlwindows] from windows

/*

Now we know how to create accounts for sqlserver using either sqlserver authetication/windows



let's talk about Credentials in sqlserver.



Credentials provide a way to allow SQL Server Authentication users to have an identity outside of SQL Server.

This is primarily used to execute code in Assemblies with EXTERNAL_ACCESS permission set.

Credentials can also be used when a SQL Server Authentication user needs access to a domain resource, such as a file location to store a backup.



Technically A Credential contains authetication information to connect resource outside of sqlsever and it contains information

about windows userid and password.



A single credential can be mapped to multiple sqlserver logins and a sqlserver login cannot mapped to multiple credentials.

*/

-- ex

/*

the following example will create credential HariCred2 using my windows

authentication doman\userid<Hariere-PC\Hariere'> with password lathe

*/

create credential HariCred2 with identity = 'Hariere-PC\Hariere', secret = 'lathe'

/*

the following example wiil create a sqlserver account and map to credentials we created above

*/

create login HariSql with password = 'test', credential = HariCred2