I am working to transfer access from a server to another server. for what I just write below tSQL to find the access list of a user.
please note that this script will not show server level access. it will show database level access only.
------------
FIND all DB level access of a user: Change the login name and run this script!
------------------
|
DECLARE @login VARCHAR(2000) = 'DOMAIN\SAzam'
CREATE TABLE #user_group (
NAME VARCHAR(2000)
,type VARCHAR(2000)
,privilege VARCHAR(2000)
,map_name VARCHAR(2000)
,original_name VARCHAR(2000)
)
INSERT INTO #user_group
EXEC Xp_logininfo @login
,'all';
DECLARE @user VARCHAR(2000)
--select top 1 @user = isnull(original_name,name)
--from #USER_CROUP
--order by original_name desc
DECLARE userlist CURSOR
FOR
SELECT DISTINCT Isnull(original_name, NAME)
FROM #user_group
CREATE TABLE #userpr (
uname VARCHAR(2000)
,dbname VARCHAR(2000)
,rname VARCHAR(2000)
)
OPEN userlist;
FETCH NEXT
FROM userlist
INTO @user
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQL VARCHAR(2000) = 'USE [?]; print db_name(); ';
SET @SQL = @SQL + 'declare @dbuser varchar(2000) = ''' + @user + '''; ' + 'SELECT @dbuser = d.name FROM sys.database_principals AS d INNER JOIN sys.server_principals AS s ON d.sid = s.sid WHERE s.name = @dbuser; '
SET @SQL = @SQL + 'insert into #UserPR SELECT u.name UName, DB_NAME() DBName, r.name RName FROM sys.database_role_members AS m left JOIN sys.database_principals AS r ON m.role_principal_id = r.principal_id left JOIN sys.database_principals AS u ON u.principal_id = m.member_principal_id WHERE u.name = @dbuser ; '
SET @SQL = @SQl + 'insert into #UserPR SELECT @dbuser UName, DB_NAME() DBName, class_desc+''-''+permission_name+''-''+state_desc PName FROM sys.database_permissions WHERE grantee_principal_id = USER_ID( @dbuser ); '
EXEC Sp_msforeachdb @SQL
FETCH NEXT
FROM userlist
INTO @user
END
SELECT *
FROM #userpr
ORDER BY dbname
,rname
CLOSE userlist
DEALLOCATE userlist
DROP TABLE #userpr
DROP TABLE #user_croup
|
