August 7, 2015 at 5:01 am
Hi guys,
Struggling to find out why this doesn't work. I'm looking to migrate a number of sql users to windows authentication, so I want a list of each login and for each login a list of all their roles. My plan is to write 2 WHILE loops that will go through each login and for each login the list of roles.
The first Row_Number is not returning the order that I want, I was expecting results like this:
1 Aaron ...... 1 db_reader
1 Aaron ...... 2 db_writer
2 Ben ....... 1 db_reader
The first row_number used doesn't seem to use the partition.
The second seems to work fine.
I know I can break this up into 2 queries but I want to understand where this is going wrong.
Code used:
SELECT
ROW_NUMBER() OVER(PARTITION BY sp.Name ORDER BY sp.name asc) as UserRowNo
, convert(char(45),sp.name) as srvLogin
, 'WIN\' + convert(char(45),sp.name) AS WinLogin
, convert(char(45),sp2.name) as srvRole
, ROW_NUMBER() OVER(PARTITION BY sp.Name ORDER BY sp.name, dbp2.name) as RoleRowNo
, convert(char(25),dbp.name) as dbUser
, convert(char(25),dbp2.name) as dbRole
FROM
sys.server_principals as sp INNER join
sys.database_principals as dbp on sp.sid=dbp.sid INNER join
sys.database_role_members as dbrm on dbp.principal_Id=dbrm.member_principal_Id INNER join
sys.database_principals as dbp2 on dbrm.role_principal_id=dbp2.principal_id left join
sys.server_role_members as srm on sp.principal_id=srm.member_principal_id left join
sys.server_principals as sp2 on srm.role_principal_id=sp2.principal_id
WHERE sp.type = 's'
AND convert(char(45),sp.name) NOT IN ('sa')
August 7, 2015 at 5:27 am
Hi,
if I understand you correctly, then you need to use DENSE_RANK, not ROW_NUMBER. ROW_NUMBER with the PARTITION BY Name means "for each new name, start counting from 1"
Try this:
DENSE_RANK() OVER(ORDER BY sp.name asc) as UserRowNo
August 7, 2015 at 6:30 am
Hi vladan, that works, thank you
I still don't understand though why the partition by sp.name order by sp.name doesn't work here.
From my understanding it should be doing the same thing
August 7, 2015 at 6:50 am
Row_Number With Partition Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. And Dense Rank Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
August 7, 2015 at 6:56 am
But why doesn't Partition By sp.Name order by sp.Name work in that regard?
I've tried dumping the result set into a temp table and doing the same (removing any problems that the joins might cause) but I get the same result
I must be understanding this wrong, but it doesn't make sense to me why the 2nd row_number will partition the result set correctly but the first doesn't
August 7, 2015 at 7:09 am
mitzyturbo (8/7/2015)
But why doesn't Partition By sp.Name order by sp.Name work in that regard?I've tried dumping the result set into a temp table and doing the same (removing any problems that the joins might cause) but I get the same result
I must be understanding this wrong, but it doesn't make sense to me why the 2nd row_number will partition the result set correctly but the first doesn't
Sometimes a picture is worth a thousand words. Just try this for example. Notice I purposely left out the partition clause as all it does is 'reset' the numbers between partitions. Once you get how the three work differently than the rest becomes obvious.
DECLARE @test-2 TABLE (name VARCHAR(20))
INSERT INTO @test-2
VALUES ('Aaron'),('Ben'),('Aaron'),('Steve'),('Brad'),('Jason'),('Brad')
SELECT ROW_NUMBER() OVER (ORDER BY name) AS RowNum, name FROM @test-2
SELECT RANK() OVER (ORDER BY name) AS RegRank, name FROM @test-2
SELECT DENSE_RANK() OVER (ORDER BY name) AS DenseRank, name FROM @test-2
August 7, 2015 at 7:27 am
Hi mitzy,
seems that problem is in understanding how PARTITION and ORDER BY works. In your code, the two ROW_NUMBER columns have the same in PARTITION clause, only the ORDER BY is different :
ROW_NUMBER() OVER(PARTITION BY sp.Name ORDER BY sp.name asc) as UserRowNo
ROW_NUMBER() OVER(PARTITION BY sp.Name ORDER BY sp.name, dbp2.name) as RoleRowNo
What does that mean? Well, since PARTITION is the same in both, you will get the same result generally, maybe just differently ordered (i.e. if there are 3 lines with name 'Aaron', they will in both cases receive numbers 1, 2 and 3 ... it is just possible, that another of the rows will have number 1 etc.).
ROW_NUMBER always numbers lines from 1 to N, by adding PARTITION BY you can say when do you want to restart numbering from 1. Without PARTITION BY, it will just go up and up. With a different PARTITION BY, it may be restarted more often - or less - but you will never be able to change the basic behavior (from 1 to N).
If you want to do anything else, like for example assign the same number to all occurences of a name, you need other related functions, like RANK or DENSE_RANK.
August 7, 2015 at 7:30 am
Go with your example:
1 Aaron ...... 1 db_reader
1 Aaron ...... 2 db_writer
2 Ben ....... 1 db_reader
The second row_number also doesn't work first they assign "1" Number for db_reader and "2" for db_writer. and again they assign "1" for db_reader.
Because the Row_Number With Partition Returns the sequential number of a row within a partition of a result set.
August 7, 2015 at 9:20 am
Thanks for all the help on this guys, all answers were good, vladan's made the most sense on this
I think what was bugging me most was that I had it in the back of my head that I was sure I had done this before using Row_Number.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply