/*
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
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
