January 13, 2009 at 7:58 am
I recently ran an sp_validatelogins via the SQL2008 Management Studio on a whole bunch of our servers and managed to get a list of invalid logins.
That part was okay. However, some of our servers are case-sensitive and there is a bug with the procedure. So I ran exec sp_helptext sp_validatelogins and got the following:
create procedure sys.sp_validatelogins
AS
-- Must be securityadmin (or sysadmin) to execute
if is_srvrolemember('securityadmin') = 0 and is_srvrolemember('sysadmin') = 0
begin
raiserror(15247,-1,-1)
return 1
end
-- Use get_sid() to determine if nt name is still valid (builtin is only available from system procs!)
select 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins
where isntname = 1 and get_sid(loginname) is null
return 0 -- sp_validatelogins
I was going to run the script outside of a procedure as:
select 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins
where isntname = 1 and get_sid(UPPER(loginname)) is null
figuring the UPPER(...) would help. This may or may not have worked, because I got hit with the error:
Msg 195, Level 15, State 10, Line 2
'get_sid' is not a recognized built-in function name.
So I placed sp_ms_marksystemobject 'get_sid' before the select and it gave me the same error. Is there a quirk to sp_ms_marksystemobject's syntax I'm missing, or get_sid for that matter?
Thanks.
Gaby
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
January 13, 2009 at 8:11 am
I am confused about what version of SQL Server you were running this on. Was it 2008 or 2005? (Note that this is a 2005 forum).
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 8:18 am
Most of our servers are 2005, but some are 2000. I'm only using 2008 to run the query across multiple servers simultaneously (eventually), but the issue is failing on my SQL 2005 box.
Cheers.
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
January 13, 2009 at 8:30 am
get_sid is, as per the comment in the proc, only available within system procs.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 13, 2009 at 8:40 am
GSquared (1/13/2009)
get_sid is, as per the comment in the proc, only available within system procs.
Okay...hmm...I tried this workaround. I created a stored proc called my_sp_validatelogins with nothing really in it, I ran exec sp_marksystemobject 'my_sp_validatelogins', and finally ran an ALTER PROC to include the get_sid portion. And it still failed. Or is there another way to make a proc a system proc?
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
January 13, 2009 at 8:41 am
I just did some testing myself and even though I can't find anything in the official documentation it seems like the function get_sid() has been removed from SQL 2005 or higher.
Even when creating a system procedure first and then do an ALTER procedure to add the get_sid function it will fail with the error " 'get_sid' is not a recognized built-in function name."
[font="Verdana"]Markus Bohse[/font]
January 13, 2009 at 8:45 am
MarkusB (1/13/2009)
I just did some testing myself and even though I can't find anything in the official documentation it seems like the function get_sid() has been removed from SQL 2005 or higher.Even when creating a system procedure first and then do an ALTER procedure to add the get_sid function it will fail with the error " 'get_sid' is not a recognized built-in function name."
Which is strange as exec sp_helptext sp_validatelogins has that function listed in it.
I'll just have to prune my resultset manually to make sure case sensitivity doesn't affect sp_validatelogins.
Thanks everyone for the input.
Gaby
Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
January 13, 2009 at 8:57 am
Gaby A. (1/13/2009)
MarkusB (1/13/2009)
I just did some testing myself and even though I can't find anything in the official documentation it seems like the function get_sid() has been removed from SQL 2005 or higher.Even when creating a system procedure first and then do an ALTER procedure to add the get_sid function it will fail with the error " 'get_sid' is not a recognized built-in function name."
Which is strange as exec sp_helptext sp_validatelogins has that function listed in it.
I'll just have to prune my resultset manually to make sure case sensitivity doesn't affect sp_validatelogins.
Thanks everyone for the input.
Gaby
Hmmm, you're right :crying:
must be something else.
MS must be doing some special magic when using this function. In SQL 2000 you can use it in your own procedures, but then it expects 2 parameters. But sp_validatelogins only provides one parameter and still it works.
In SQL 2005 any attempt to use this function fails, except in sp_validatelogin.
[font="Verdana"]Markus Bohse[/font]
January 13, 2009 at 8:57 am
There is no supported way for us to make a system proc is SQL Server 2005.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 13, 2009 at 11:17 am
MarkusB (1/13/2009)
Gaby A. (1/13/2009)
MarkusB (1/13/2009)
I just did some testing myself and even though I can't find anything in the official documentation it seems like the function get_sid() has been removed from SQL 2005 or higher.Even when creating a system procedure first and then do an ALTER procedure to add the get_sid function it will fail with the error " 'get_sid' is not a recognized built-in function name."
Which is strange as exec sp_helptext sp_validatelogins has that function listed in it.
I'll just have to prune my resultset manually to make sure case sensitivity doesn't affect sp_validatelogins.
Thanks everyone for the input.
Gaby
Hmmm, you're right :crying:
must be something else.
MS must be doing some special magic when using this function. In SQL 2000 you can use it in your own procedures, but then it expects 2 parameters. But sp_validatelogins only provides one parameter and still it works.
In SQL 2005 any attempt to use this function fails, except in sp_validatelogin.
There are a *TON* of feature like this "hidden" from us (the users) in SQL 2005 and above... oh well...
* Noel
January 18, 2009 at 4:30 am
December 15, 2009 at 12:38 pm
For anyone else researching the "sp_MS_marksystemobject", I found the following excerpt from Kalen Dalaney:
http://sqlblog.com/blogs/kalen_delaney/archive/2008/08/10/geek-city-system-objects.aspx
Kalen says:
...The objectproperty function has an argument called 'IsSystemTable', that will show you which tables are system tables, but won't do anything for other system objects.
The objectproperty function also has an argument called 'IsMSShipped', which corresponds to a column in the SQL 2005 metadata view sys.objects called 'is_ms_shipped'.
SELECT *
FROM sys.objects
WHERE OBJECTPROPERTY(object_id, 'IsMSShipped') = 1;
But just because something is shipped by Microsoft, is it really a system object? There is an undocumented procedure called sp_MS_marksystemobject, that would change the properties of any object you created so that its 'is_ms_shipped' property would also show a value of 1. ...
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply