As a DBA or Developer, it is very important to know how much access I have or a user has?
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 |