Here We are creating 2 Windows users Name Adven\Rakesh & adven\kushagra and giving db_datareader database role on DB AdventureWorks2014
use master;
if(SUSER_ID('Adven\Rakesh') is NULL)
begin
create login [Adven\Rakesh] from WINDOWS;
end
if(SUSER_ID('adven\kushagra') is NULL)
begin
create login [adven\kushagra] from WINDOWS;
end
use AdventureWorks2014;
if not exists (select 1 from sys.database_principals where name = 'adven\rakesh')
create user [adven\rakesh] for login [adven\rakesh] with default_schema = [dbo];
exec sp_addrolemember @membername = N'adven\rakesh', @rolename = N'db_datareader';
if not exists (select 1 from sys.database_principals where name = 'adven\kushagra')
create user [adven\kushagra] for login [adven\kushagra] with default_schema = [dbo];
exec sp_addrolemember @membername = N'adven\kushagra', @rolename = N'db_datareader';
After giving permission, if i have to check script has executed and
1. logins & User has been created or not
2.permission has been added successfully or not execute below script
use AdventureWorks2014;
go
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
and isnull (DP2.name, 'No members') in ('adven\kushagra','Adven\Rakesh')
ORDER BY DP1.name;
~~~~~~~~~~~~~~~~~~~~~~
Server and database Role:-
Ans : kindly reply answer in comment section..
kindly watch this space, i will keep adding more logical script related to SQL Server logins & Users