As an example if we want to grant DBO access to an user across all databases in a server, below query can be used.
SELECT d.name, 0 as "flag" INTO #dbnames FROM master..sysdatabases d
DECLARE @dbname varchar(20)
DECLARE @droprole varchar(300)
DECLARE @addrole varchar(300)
DECLARE @err INT
WHILE EXISTS (SELECT 1 FROM #dbnames WHERE flag = 0)
BEGIN
SET ROWCOUNT 1
SELECT @dbname = name FROM #dbnames WHERE flag = 0
SET ROWCOUNT 0
SELECT @dbname
SELECT @droprole = @dbname + "..sp_dropuser 'my_user'"
SELECT @droprole
EXEC (@droprole)
SELECT @addrole = @dbname + "..sp_addalias 'my_user','dbo'"
SELECT @addrole
EXEC (@addrole)
UPDATE #dbnames SET flag = 1 WHERE name = @dbname
END
SELECT d.name, 0 as "flag" INTO #dbnames FROM master..sysdatabases d
DECLARE @dbname varchar(20)
DECLARE @droprole varchar(300)
DECLARE @addrole varchar(300)
DECLARE @err INT
WHILE EXISTS (SELECT 1 FROM #dbnames WHERE flag = 0)
BEGIN
SET ROWCOUNT 1
SELECT @dbname = name FROM #dbnames WHERE flag = 0
SET ROWCOUNT 0
SELECT @dbname
SELECT @droprole = @dbname + "..sp_dropuser 'my_user'"
SELECT @droprole
EXEC (@droprole)
SELECT @addrole = @dbname + "..sp_addalias 'my_user','dbo'"
SELECT @addrole
EXEC (@addrole)
UPDATE #dbnames SET flag = 1 WHERE name = @dbname
END
No comments:
Post a Comment