Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Granting access to one db to users/roles of another
- USE [database1]
- GRANT EXECUTE ON [database2].[dbo].[CUSTOM_PROCEDURE] TO [applicationrole1]
- OR
- USE [database2]
- GRANT EXECUTE ON [dbo].[CUSTOM_PROCEDURE] TO [database1].[dbo].[applicationrole1]
- INSERT INTO Database2.sys.database_principals
- SELECT * FROM Database1.sys.database_principals
- WHERE [type] = 'S'
- use master
- go
- create login testuser with password = 'mypassword123'
- go
- use test
- go
- create role reporting
- grant select on something to reporting -- grant your permissions here
- create user testuser for login testuser
- exec sp_addrolemember 'reporting', 'testuser'
- go
- use test2
- go
- create role reporting
- grant select on something2 to reporting -- grant your permissions here
- create user testuser for login testuser
- exec sp_addrolemember 'reporting', 'testuser'
- go
- select * from something
- select * from test2.dbo.something2
- declare @sql nvarchar(max), @username nvarchar(50), @password nvarchar(50)
- -- ########## SET PARAMETERS HERE
- SET @username = N'testguy'
- SET @password = N'test123'
- -- ########## END SET PARAMETERS
- set @sql = N'USE master; CREATE LOGIN [' + @username + N'] WITH PASSWORD = N''' + @password + N'''; USE database1; CREATE USER [' + @username + N'] FOR LOGIN [' + @username + N']; EXEC sp_addrolemember ''reporting'', ''' + @username + N'''; USE database2; CREATE USER [' + @username + N'] FOR LOGIN [' + @username + N']; EXEC sp_addrolemember ''reporting'', ''' + @username + N''';'
- exec sp_executesql @sql
- declare @login nvarchar(50), @user1 nvarchar(50), @user2 nvarchar(50), @sql nvarchar(max), @rolename nvarchar(50)
- SET @rolename = 'reporting'
- declare c cursor for
- select sp.name as login, dp1.name as user1, dp2.name as user2 from sys.server_principals as sp
- left outer join database1.sys.database_principals as dp1 on sp.sid = dp1.sid
- left outer join database2.sys.database_principals as dp2 on sp.sid = dp2.sid
- where sp.type = 'S'
- and sp.is_disabled = 0
- open c
- fetch next from c into @login, @user1, @user2
- while @@FETCH_STATUS = 0 begin
- -- create user in db1
- if (@user1 is null) begin
- SET @sql = N'USE database1; CREATE USER [' + @login + N'] FOR LOGIN [' + @login + N'];'
- EXEC sp_executesql @sql
- end
- -- ensure user is member of role in db1
- SET @sql = N'USE database1; EXEC sp_addrolemember '''+ @rolename + ''', ''' + @login + N''';'
- EXEC sp_executesql @sql
- -- create user in db2
- if (@user2 is null) begin
- SET @sql = N'USE database2; CREATE USER [' + @login + N'] FOR LOGIN [' + @login + N'];'
- EXEC sp_executesql @sql
- end
- -- ensure user is member of role in db2
- SET @sql = N'USE database2; EXEC sp_addrolemember '''+ @rolename + ''', ''' + @login + N''';'
- EXEC sp_executesql @sql
- fetch next from c into @login, @user1, @user2
- end
- close c
- deallocate c
Add Comment
Please, Sign In to add comment