February 6, 2012 at 3:59 am
Hi,
I am running this query :-
SELECT users.name,roles.name
from sys.database_principals users
inner join sys.database_role_members link
on link.member_principal_id = users.principal_id
inner join sys.database_principals roles
on roles.principal_id = link.role_principal_id
order by users.name
to get the users in database and their permission. But the problem with this query is, it gives me the result as :-
************************************
AcntName Permission
USer1 db_dataReader
USer1 db_datawriter
USer2 db_dataReader
USer2 db_dataReader
USer2 db_ddladmin
USer3 db_dataReader
USer3 db_dataReader
USer3 db_owner
USer4 db_dataReader
USer4 db_dataReader
*********************************************
need your help to write the query, to get the result group by AcntName so that Result looks like :-
***************************
AcntName Permission
USer1 db_datawriter,db_dataReader
USer2 db_datawriter,db_dataReader, db_ddladmin
*******************************************
Thanks.
----------
Ashish
February 6, 2012 at 4:18 am
Lots of ways.
http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 5:20 am
Thanks Gail. But till date, I am not able to figure out the script writing using CTE.
Need help with this code please.
----------
Ashish
February 6, 2012 at 5:28 am
How about the XML method(s)?
February 6, 2012 at 5:29 am
Did you read the article? There's lots of ways listed and covered in that and lots of sample code. CTE is far from a requirement.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 5:35 am
Thanks Gail, so finally here is my code :-
DECLARE @tbl TABLE (acntname VARCHAR(800), Permisson VARCHAR(800))
SET NOCOUNT ON
DECLARE @C VARCHAR(800), @p VARCHAR(800), @cNext VARCHAR(800), @pNext VARCHAR(40)
DECLARE c CURSOR FOR
SELECT users.name as AcntName ,roles.name as Permisson
from sys.database_principals users
inner join sys.database_role_members link
on link.member_principal_id = users.principal_id
inner join sys.database_principals roles
on roles.principal_id = link.role_principal_id
order by users.name ;
OPEN c ;
FETCH NEXT FROM c INTO @cNext, @pNext ;
SET @C = @cNext ;
WHILE @@FETCH_STATUS = 0 BEGIN
IF @cNext > @C BEGIN
INSERT @tbl SELECT @C, @p ;
SELECT @p = @PNext, @C = @cNext ;
END ELSE
SET @p = COALESCE(@p + ',', SPACE(0)) + @pNext ;
FETCH NEXT FROM c INTO @cNext, @pNext
END
INSERT @tbl SELECT @C, @p ;
CLOSE c ;
DEALLOCATE c ;
SELECT * FROM @tbl ;
Additional thanks to Gail for forcing me to do myself 🙂
----------
Ashish
February 6, 2012 at 5:46 am
Ow, that's probably the slowest of all of the options that the article discussed, and the article even stated:
Because they are generally resource intensive, procedural and inefficient, one should strive to avoid cursors or loop based solutions in general Transact SQL programming.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2012 at 5:57 am
Let's not use a cursor when we can avoid it 😀
--Method 1
SELECT NAME, COALESCE(STUFF((SELECT ', ' + roleName
FROM (SELECT users.NAME, roles.NAME AS roleName
FROM sys.database_principals users
INNER JOIN sys.database_role_members link ON link.member_principal_id = users.principal_id
INNER JOIN sys.database_principals roles ON roles.principal_id = link.role_principal_id) t2
WHERE t2.NAME = t1.NAME
FOR XML PATH('')), 1, 2, ''), '') AS roleNames
FROM (SELECT users.NAME, roles.NAME AS roleName
FROM sys.database_principals users
INNER JOIN sys.database_role_members link ON link.member_principal_id = users.principal_id
INNER JOIN sys.database_principals roles ON roles.principal_id = link.role_principal_id) t1
ORDER BY t1.NAME;
--Method 2
WITH CTE AS (SELECT users.NAME,
ROW_NUMBER() OVER( PARTITION BY users.NAME ORDER BY roles.NAME) AS rn,
CAST(roles.NAME AS VARCHAR(8000)) AS roleName
FROM sys.database_principals users
INNER JOIN sys.database_role_members link ON link.member_principal_id = users.principal_id
INNER JOIN sys.database_principals roles ON roles.principal_id = link.role_principal_id),
Anchor AS (SELECT NAME, rn, roleName
FROM CTE
WHERE rn = 1 ),
Recur AS (SELECT NAME, rn, roleName
FROM Anchor
UNION ALL
SELECT a.Name, a.rn,
Recur.roleName + ', ' + a.roleName
FROM CTE a
INNER JOIN Recur ON a.NAME = Recur.NAME AND a.rn = Recur.rn + 1 )
SELECT NAME, MAX(roleName) AS roleNames
FROM Recur
GROUP BY NAME;
Countless other ways, but there's two to get you started.
February 6, 2012 at 6:10 am
Many thanks for additional codes and comments. The option to select the cursor was just easy for me to replicate and my query is not resource intensive.
I need to brush-up my skills on CTE and will re-write the code. Fully agree that I should avoid cursor.
But as of now, my self made food is giving the best taste ( doesn't matter how bad its for others) :))
----------
Ashish
February 7, 2012 at 3:06 am
Here is the updated script to capture the information for all user database in the server using suggested CTE method in earlier reply (but cursor still not going out of my mind) :-
create table #tmpPermission ( dbname varchar(200), name varchar(200),permission varchar(2000))
go
declare @sql varchar(5000) = null
declare @dbname varchar(50)
declare @s-2 varchar(max)
declare dbcursor cursor for
select name from sys.databases where database_id > 4 and state_desc = 'online' order by name
open dbcursor
fetch next from dbcursor into @dbname
while @@FETCH_STATUS = 0
begin
set @sql = 'use [' + @dbname + '];'
set @sql = @sql + ' WITH CTE AS (SELECT db_name() as dbname, users.NAME,
ROW_NUMBER() OVER( PARTITION BY users.NAME ORDER BY roles.NAME) AS rn,
CAST(roles.NAME AS VARCHAR(8000)) AS roleName
FROM sys.database_principals users
INNER JOIN sys.database_role_members link ON link.member_principal_id = users.principal_id
INNER JOIN sys.database_principals roles ON roles.principal_id = link.role_principal_id),
Anchor AS (SELECT dbname, NAME, rn, roleName
FROM CTE
WHERE rn = 1 ),
Recur AS (SELECT dbname, NAME, rn, roleName
FROM Anchor
UNION ALL
SELECT a.dbname, a.Name, a.rn,
Recur.roleName + ' +'''' + ', ' +'''' +' + a.roleName
FROM CTE a
INNER JOIN Recur ON a.NAME = Recur.NAME AND a.rn = Recur.rn + 1 )
SELECT dbname, NAME, MAX(roleName) AS roleNames
FROM Recur where name not like '+''''+'dbo'+''''+
'GROUP BY NAME , dbname ;'
--print @sql
insert into #tmpPermission ( dbname , name ,permission ) exec(@sql)
--set @sql = ' '
fetch next from dbcursor into @dbname
end
CLOSE dbcursor ;
DEALLOCATE dbcursor ;
select * from #tmpPermission
go
DROP TABLE #tmpPermission
Hope this script will save your precious mins.
----------
Ashish
February 7, 2012 at 3:22 am
crazy4sql (2/7/2012)
Here is the updated script to capture the information for all user database in the server using suggested CTE method in earlier reply (but cursor still not going out of my mind) :-
create table #tmpPermission ( dbname varchar(200), name varchar(200),permission varchar(2000))
go
declare @sql varchar(5000) = null
declare @dbname varchar(50)
declare @s-2 varchar(max)
declare dbcursor cursor for
select name from sys.databases where database_id > 4 and state_desc = 'online' order by name
open dbcursor
fetch next from dbcursor into @dbname
while @@FETCH_STATUS = 0
begin
set @sql = 'use [' + @dbname + '];'
set @sql = @sql + ' WITH CTE AS (SELECT db_name() as dbname, users.NAME,
ROW_NUMBER() OVER( PARTITION BY users.NAME ORDER BY roles.NAME) AS rn,
CAST(roles.NAME AS VARCHAR(8000)) AS roleName
FROM sys.database_principals users
INNER JOIN sys.database_role_members link ON link.member_principal_id = users.principal_id
INNER JOIN sys.database_principals roles ON roles.principal_id = link.role_principal_id),
Anchor AS (SELECT dbname, NAME, rn, roleName
FROM CTE
WHERE rn = 1 ),
Recur AS (SELECT dbname, NAME, rn, roleName
FROM Anchor
UNION ALL
SELECT a.dbname, a.Name, a.rn,
Recur.roleName + ' +'''' + ', ' +'''' +' + a.roleName
FROM CTE a
INNER JOIN Recur ON a.NAME = Recur.NAME AND a.rn = Recur.rn + 1 )
SELECT dbname, NAME, MAX(roleName) AS roleNames
FROM Recur where name not like '+''''+'dbo'+''''+
'GROUP BY NAME , dbname ;'
--print @sql
insert into #tmpPermission ( dbname , name ,permission ) exec(@sql)
--set @sql = ' '
fetch next from dbcursor into @dbname
end
CLOSE dbcursor ;
DEALLOCATE dbcursor ;
select * from #tmpPermission
go
DROP TABLE #tmpPermission
Hope this script will save your precious mins.
Shall we get rid of the cursor? Yes, I think we should. 😀
CREATE TABLE #tmpPermission (dbname VARCHAR(200), NAME VARCHAR(200), permission VARCHAR(2000));
DECLARE @sql NVARCHAR(MAX);
SELECT @sql = COALESCE(@sql,'') + ';' + CHAR(13) + CHAR(10) +
'USE ' + QUOTENAME(name) + ';' + CHAR(13) + CHAR(10) +
'WITH CTE AS (SELECT db_name() as dbname, users.NAME,' + CHAR(13) + CHAR(10) +
'ROW_NUMBER() OVER( PARTITION BY users.NAME ORDER BY roles.NAME) AS rn,' + CHAR(13) + CHAR(10) +
'CAST(roles.NAME AS VARCHAR(8000)) AS roleName' + CHAR(13) + CHAR(10) +
'FROM sys.database_principals users' + CHAR(13) + CHAR(10) +
'INNER JOIN sys.database_role_members link ON link.member_principal_id = users.principal_id' + CHAR(13) + CHAR(10) +
'INNER JOIN sys.database_principals roles ON roles.principal_id = link.role_principal_id),' + CHAR(13) + CHAR(10) +
'Anchor AS (SELECT dbname, NAME, rn, roleName' + CHAR(13) + CHAR(10) +
'FROM CTE' + CHAR(13) + CHAR(10) +
'WHERE rn = 1 ),' + CHAR(13) + CHAR(10) +
'Recur AS (SELECT dbname, NAME, rn, roleName' + CHAR(13) + CHAR(10) +
'FROM Anchor' + CHAR(13) + CHAR(10) +
'UNION ALL' + CHAR(13) + CHAR(10) +
'SELECT a.dbname, a.NAME, a.rn,' + CHAR(13) + CHAR(10) +
'Recur.roleName + ' +'''' + ', ' +'''' +' + a.roleName' + CHAR(13) + CHAR(10) +
'FROM CTE a' + CHAR(13) + CHAR(10) +
'INNER JOIN Recur ON a.NAME = Recur.NAME AND a.rn = Recur.rn + 1 )' + CHAR(13) + CHAR(10) +
'SELECT dbname, NAME, MAX(roleName) AS roleNames' + CHAR(13) + CHAR(10) +
'FROM Recur WHERE NAME NOT LIKE '+''''+'dbo'+'''' + CHAR(13) + CHAR(10) +
'GROUP BY NAME, dbname'
FROM sys.databases
WHERE database_id > 4 AND state_desc = 'online'
ORDER BY name;
SET @sql = STUFF(@sql,1,3,'');
INSERT INTO #tmpPermission
EXECUTE sp_executesql @sql;
SELECT * FROM #tmpPermission;
February 7, 2012 at 3:36 am
Shall we get rid of the cursor? Yes, I think we should. 😀
Yes mate, finally the cursor out of mind and scope in the query. Thanks for the code and help.
----------
Ashish
February 7, 2012 at 4:31 am
Cadavre (2/7/2012)
Shall we get rid of the cursor? Yes, I think we should. 😀
The recursive CTE is still iterating, it's just a lot more subtle about it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2012 at 4:34 am
GilaMonster (2/7/2012)
Cadavre (2/7/2012)
Shall we get rid of the cursor? Yes, I think we should. 😀The recursive CTE is still iterating, it's just a lot more subtle about it.
Totally agreed, that's why in my first post I showed the XML PATH method as "Method 1". But the OP chose the CTE instead, either after testing both methods or because he understood the CTE better.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply