August 13, 2012 at 4:07 am
I'd like to be able to list all SQL Logins. However there are some logins that are builtin such as sa and ##.....##.
At the present, I have this query:
SELECT *
FROM sys.server_principals AS sp
WHERE sp.type='S'
AND sp.name <> 'sa'
AND NOT sp.name LIKE '##%'
but I don't know if this is really the best solution. I was hoping that there was a column that indicates whether a sql login is built-in so I can exclude with certainty.
Suggestions?
August 13, 2012 at 4:26 am
This was removed by the editor as SPAM
August 13, 2012 at 4:30 am
Thanks but this still includes the 'sa' and '##...##' logins and I don't see any columns that would indicate if the login is a builtin login or not.
August 13, 2012 at 4:55 am
I think principal_id >= xx should work. Lower numbers are usually reserved for system/builtin objects.
August 13, 2012 at 5:01 am
Yes, I observed this, but my concern was that this amounts to using a magic numbers which may work today but not tomorrow. I have the same problem with "name <> 'sa' AND NOT name LIKE '##%'" -- they work right now but I don't want to rely on any assumptions that they'll still work for new editions where they decide to add/take away builtins.
It does sounds, though, there is no way of identifying a principal as a built in or not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply