If i have to grant access to Database using T-SQL and also verify if user already exist in SQL ,
If user don't exist then create account first and then grant access to database.
If user exist, just grant access to database.
I only create users in SQL. Not in Windows.
What will be by T-SQL Query to achieve above. ?
Try to create your login first, and the your user. This code first checks where all your users are assigned to which databases. Afterwards it checks if there is a login created and then it checks if the user exists. It is also set dynamically to you can just enter a DBName.
Example
---Get information on which users has access to my datase
set nocount on
declare @permission table (
Database_Name sysname,
User_Role_Name sysname
)
declare @dbs table (dbname sysname)
declare @Next sysname
insert into @dbs
select name from sys.databases order by name
select top 1 @Next = dbname from @dbs
while (@@rowcount<>0)
begin
insert into @permission
exec('use [' + @Next + ']
SELECT ''' + @Next + ''', a.name as ''User or Role Name''
FROM [' + @Next + '].sys.database_principals a
left join [' + @Next + '].sys.database_permissions d on a.principal_id = d.grantee_principal_id
order by a.name, d.class_desc')
delete @dbs where dbname = @Next
select top 1 @Next = dbname from @dbs
end
set nocount off
--Declare my Variables
Declare @DBName VARCHAR(30)
DECLARE @IsWindowsUser int = 0
DECLARE @UserName nvarchar(50) = 'hestt4545tt'
DECLARE @PassWord nvarchar(50) = 'hest123123'
DECLARE @LoginExists int
DECLARE @UserExists int
DECLARE @LoginSQL nvarchar(MAX)
DECLARE @UserSQL nvarchar(MAX)
DECLARE @MultiDatabase nvarchar(max) ='LegOgSpass,LoadConfiguration'
--SET @DBName = 'LegOgSpass'
DECLARE myCursor CURSOR FOR
select [value] from string_split(@MultiDatabase,',')
OPEN myCursor
FETCH NEXT FROM myCursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
exec('USE '+ @DBName)
IF @IsWindowsUser = 0
BEGIN
/* Users are typically mapped to logins, as OP's question implies,
so make sure an appropriate login exists. */
SET @LoginExists = (Select count(principal_id) FROM sys.server_principals WHERE name = @UserName)
---Check if login exists - else create it
IF @LoginExists = 0
BEGIN
/* Syntax for SQL server login. See BOL for domain logins, etc. */
SET @LoginSQL = 'USE ' +@DBName + ' CREATE LOGIN '+@UserName +' WITH PASSWORD = '''+@PassWord+''''
PRINT 'Login doesnt exists'
EXEC (@LoginSQL)
PRINT 'Therefore i make a new login now'
SET @LoginExists = (Select count(principal_id) FROM sys.server_principals WHERE name = @UserName)
IF @LoginExists = 1
PRINT 'Login is now created and exists'
BEGIN
SET @UserExists = (SELECT count(principal_id) FROM sys.database_principals WHERE name = @UserName)
IF @UserExists =0
PRINT 'User doesnt exists'
BEGIN
SET @UserSQL = 'USE ' +@DBName+ ' CREATE USER ' +@UserName +' FOR LOGIN '+@UserName
EXEC (@UserSQL)
PRINT 'User is now created'
END
END
END
ELSE
BEGIN
SET @UserExists = (select COUNT(distinct User_Role_Name) from @permission where User_Role_Name =@UserName and Database_Name = @DBName)
IF @UserExists =0
BEGIN
PRINT 'Login already exists - go create user for access to database'
SET @UserSQL = 'USE ' +@DBName+ ' CREATE USER ' +@UserName +' FOR LOGIN '+@UserName
EXEC (@UserSQL)
PRINT 'User is now created'
END
END
END
ELSE
BEGIN
SET @LoginExists = (Select count(principal_id) FROM sys.server_principals WHERE name = REPLACE(REPLACE(@UserName,'[',''),']',''))
---Check if login exists - else create it
IF @LoginExists = 0
BEGIN
/* Syntax for SQL server login. See BOL for domain logins, etc. */
SET @LoginSQL = 'USE ' +@DBName + ' CREATE LOGIN '+@UserName +' FROM WINDOWS'
PRINT 'Windows Login doesnt exists'
EXEC (@LoginSQL)
PRINT 'Therefore i make a new window login now'
SET @LoginExists = (Select count(principal_id) FROM sys.server_principals WHERE name = @UserName)
IF @LoginExists = 1
PRINT 'Windows Login is now created and exists'
BEGIN
SET @UserExists = (SELECT count(principal_id) FROM sys.database_principals WHERE name = @UserName)
IF @UserExists =0
PRINT 'User doesnt exists'
BEGIN
SET @UserSQL = 'USE ' +@DBName+ ' CREATE USER ' +@UserName +' FOR LOGIN '+@UserName
EXEC (@UserSQL)
PRINT 'User is now created'
END
END
END
ELSE
BEGIN
SET @UserExists = (select COUNT(distinct User_Role_Name) from @permission where User_Role_Name =REPLACE(REPLACE(@UserName,'[',''),']','') and Database_Name = @DBName)
IF @UserExists =0
BEGIN
PRINT 'Window Login already exists - go create user for access to database'
SET @UserSQL = 'USE ' +@DBName+ ' CREATE USER ' +@UserName +' FOR LOGIN '+@UserName
EXEC (@UserSQL)
PRINT 'User is now created'
END
END
END
FETCH NEXT FROM myCursor INTO @DBName
--Ending cursor
END
CLOSE myCursor
DEALLOCATE myCursor
