☰

如何创建对现有数据库和新建数据库具有读写权限的 SQL 用户

tag-icon • tag-icon sql-server permissions user-management user-accounts
如何创建对现有数据库和新建数据库具有读写权限的 SQL 用户

如何在 SQL Server 中设置具有读写特权的用户，以便该用户自动对新创建的数据库具有读写权限。这应该适用于所有用户数据库 - 包括已创建的数据库或新创建的数据库

答案1

我正好有这个，它也许能满足你的需要。

Declare
    @username sysname,
    @password varchar(255),
    @SQL nvarchar(max),
    @RowsToProcess int,
    @CurrentRow int 

set @username = 'youruser'
set @password = 'theirpassword'


    SET @SQL = 'CREATE LOGIN ' + @username + ' WITH PASSWORD = ''' + @password + ''''
    EXECUTE(@SQL);

CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), sqlcmd nvarchar(max) )  

insert into #maintenancetemp    
SELECT 'USE ' + QUOTENAME(NAME) + '; 
        IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @username + ''') CREATE USER ' + QUOTENAME(@username)
       + ' FOR LOGIN ' + QUOTENAME(@username)
       + ' WITH DEFAULT_SCHEMA=[dbo];
    EXEC sys.sp_addrolemember ''db_datareader'',''' + @username + ''';
    EXEC sys.sp_addrolemember ''db_datawriter'', ''' + @username + ''''
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE' 


SET @RowsToProcess=@@ROWCOUNT

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT @SQL = sqlcmd FROM #maintenancetemp WHERE RowID=@CurrentRow
        EXEC SP_EXECUTESQL @SQL
        --print @SQL                
END

drop table #maintenancetemp

这对新创建的数据库没有帮助，但您也可以运行夜间作业来捕获这些数据库：

Declare
    @username sysname,
    @SQL nvarchar(max),
    @RowsToProcess int,
    @CurrentRow int 

set @username = 'youruser'


CREATE TABLE #maintenancetemp (RowID int not null primary key identity(1,1), sqlcmd nvarchar(max) )  

insert into #maintenancetemp    
SELECT 'USE ' + QUOTENAME(NAME) + '; 
        IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''' + @username + ''') CREATE USER ' + QUOTENAME(@username)
       + ' FOR LOGIN ' + QUOTENAME(@username)
       + ' WITH DEFAULT_SCHEMA=[dbo];
    EXEC sys.sp_addrolemember ''db_datareader'',''' + @username + ''';
    EXEC sys.sp_addrolemember ''db_datawriter'', ''' + @username + ''''
FROM   sys.databases
WHERE  database_id > 4
       AND state_desc = 'ONLINE' 
       AND create_date > dateadd(D, -1, GETDATE())


SELECT @RowsToProcess = COUNT(*) from #maintenancetemp

SET @CurrentRow=0
WHILE @CurrentRow<@RowsToProcess
BEGIN
    SET @CurrentRow=@CurrentRow+1
    SELECT @SQL = sqlcmd FROM #maintenancetemp WHERE RowID=@CurrentRow
        EXEC SP_EXECUTESQL @SQL             
END

drop table #maintenancetemp

答案2

你可以以此作为开始 -

EXEC master..sp_MSForeachdb '
USE [?]
    IF ''?'' <> ''master'' AND ''?'' <> ''model'' AND ''?'' <> ''msdb'' AND ''?'' <> ''tempdb'' AND ''?'' <> ''ReportServer'' AND ''?'' <> ''ReportServerTempDB''  
    BEGIN
    print ''?''
    IF EXISTS (SELECT name FROM sysusers WHERE name = ''domainname\someuser'') DROP USER [domainname\someuser]
    CREATE USER [domain\someuser] FOR LOGIN [domain\someuser]
    EXEC sp_addrolemember ''db_datareader'', ''domain\someuser''
    EXEC sp_addrolemember ''db_datawriter'', ''domain\someuser''

end
'

相关内容