如何在SQL Server中设置读写特权用户，以便该用户自动读取新创build的数据库的写权限。 这应该适用于所有的用户数据库 – 包括那些已经创build的或只是新创build的数据库
我碰巧躺在这里，这可能适合你的需求。
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
这对新创build的数据库没有帮助，但是您也可以运行夜间工作来捕获这些数据库：
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
这可能可以作为一个开始 –
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 '