Modifying a system stored procedure

Question

Modifying a system stored procedure

Senchi

Ten Centuries

Points: 1184
More actions
December 14, 2016 at 1:57 pm

#319752

Using MS SQL Server 2014
I am trying to modify the system stored procedure sp_addlogin, but getting the following error:
Msg 208, Level 16, State 6, Procedure sp_addlogin, Line 8
Invalid object name 'sys.sp_addlogin'.
I can open the stored procedure in Management Studio but not alter it using the ALTER PROCEDURE statement. I want to comment these lines so I can run the procedure with my transaction.:
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sys.sp_addlogin')
return (1)
end

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

Senchi (12/14/2016)
Using MS SQL Server 2014
I am trying to modify the system stored procedure sp_addlogin, but getting the following error:
Msg 208, Level 16, State 6, Procedure sp_addlogin, Line 8
Invalid object name 'sys.sp_addlogin'.
I can open the stored procedure in Management Studio but not alter it using the ALTER PROCEDURE statement. I want to comment these lines so I can run the procedure with my transaction.:
set implicit_transactions off
IF (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sys.sp_addlogin')
return (1)
end

You don't. It's not supported, not recommended and will break in the future (patches, upgrades, etc.)

If you need the similar functionality, use the system stored procedure as a template to create your own. You can put it in master and mark it as a system object if you want so that it acts like system stored procedures. But don't change existing system objects.

Sue

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You can't change the system procedures.

You're getting the error because they're not stored in master, they're stored in the hidden resource database

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Senchi Ten Centuries Points: 1184 More actions · Answer 3

Sue, I tried your suggestion...

I copied the sp_addlogin

and created a new one.

However, I got the error on :

--execute sp_hexadecimal @passwdbin, @hextext OUT

and

--execute sys.sp_hexadecimal @sid, @hextext OUT

No such stored procedure ...

I commented the lines and it compiled ok. Now I wonder if this is OK ???

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

It probably won't work correctly.

Why are you using sp_addlogin though? It's deprecated, has been since SQL 2005 and shouldn't be used any longer. It doesn't have support for any features added since SQL 2005 (password policy, etc).

You should be using CREATE LOGIN.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Senchi (12/15/2016)
Sue, I tried your suggestion...
I copied the sp_addlogin
and created a new one.
However, I got the error on :
--execute sp_hexadecimal @passwdbin, @hextext OUT
and
--execute sys.sp_hexadecimal @sid, @hextext OUT
No such stored procedure ...
I commented the lines and it compiled ok. Now I wonder if this is OK ???

No it's not. When you use something as a template, you generally don't want to copy it word for word but rather use it as a guide to performing something. And when you use things that you know nothing about, it's fairly useless as you can't maintain it.

But as Gail pointed out, sp_addlogin is deprecated - create user is what is used instead.

It may be time to rethink whatever you are trying to accomplish - your digging yourself a hole at this point.

Sue