T-SQL语句授予访问权限并创建帐户

Question

如果我必须使用T-SQL授予对数据库的访问权，并且还要验证用户是否已经存在于SQL中，如果用户不存在，那么首先创建帐户，然后授予对数据库的访问权。

如果存在用户，只需授予对数据库的访问权限。

我只在SQL中创建用户。在Windows里不行。

什么将由T-SQL查询来实现以上。？

Answer 1

尝试先创建您的登录名，然后创建您的用户。此代码首先检查分配给哪个数据库的所有用户的位置。之后，它检查是否创建了登录，然后检查用户是否存在。它也是动态设置的，您只需输入一个DBName。

示例

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