【SQLServer】创建可以访问sqlserver实例中所有数据库的用户

1.创建一个login

?

1 2 3 4

USE [master] GO CREATE LOGIN [abce] WITH PASSWORD=N'Tips_DemoUser@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON GO

　　

2.授权

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

USE master GO DECLARE     @dbname VARCHAR ( 50 ) DECLARE     @statement NVARCHAR ( MAX ) DECLARE     db_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT     name FROM     MASTER.dbo.sysdatabases WHERE     name NOT IN ( 'master', 'model', 'msdb', 'tempdb', 'distribution' ) OPEN db_cursor FETCH NEXT FROM     db_cursor INTO @dbname WHILE         @@FETCH_STATUS = 0 BEGIN         SELECT             @statement = 'use ' +@dbname + ';' + 'CREATE USER [abce]         FOR LOGIN [abce]; EXEC sp_addrolemember N''db_datareader'',         [abce];EXEC sp_addrolemember N''db_datawriter'', [abce]' EXEC sp_executesql @statement FETCH NEXT         FROM         db_cursor INTO @dbname     END CLOSE db_cursor DEALLOCATE db_cursor