procedure run by assigned user id 1

[babeo]

Hi
How do I find out a userid is used to run a procedure or procedures ? the reason to ask is because I want to change the password for this particular userid, but I have to find out any procedure/programs that is used this particular id to run or I will have error.

 

[JMCraig]

Probably the simplest way to get the info you want is to run sp_helprotect <user name> this will give you a full list of what the user has permission to operate on.

As you get things set up, you'll find your life is easier if you define groups and give permissions to the group (or use roles if appropriate). That makes the whole question of changing a password less of an issue.

BOL,

J M Craig
nsjmcraig@netscape.net

 

[babeo]

Hi Craig

Thanks for the answer.
Actually the user that I want to check is 'sa' and it seems the command does not work for this userid. It works for other userids or roles as your suggestion. Is there any idea why?

 

[JMCraig]

Yes, with 'SA' things are a bit different. 'SA' isn't a user in the conventional sense, but in any DB that 'SA' is logged into (via the use <db name> command or through the DNS, if you're using one), 'SA' is an alias for the pseudo-user 'dbo'.

sp_helprotect should give you info for the user 'dbo', but that's not going to help you much because they'll likely have permission to do anything.

However, given that you want to change the 'SA' password, you may not realize that any permissions on the DB will be not be affected. Your 'SA' login probably can do anything to anything in your DB (unless you specifically prevent that). What you'll need to do is find any scripts you have that contain the 'SA' password and change those--nothing on the DB will change when you adjust the 'SA' password.

(In fact, this is the case for any user's password.)

HTH,

John Craig
Alpha-G Consulting, LLC
nsjmcraig@netscape.net

 

[babeo]

Hi Craig

Yes, it is exactly what I mean in your paragraph &quot;However...&quot;. I just want to change the password of the user (in this case the sa user) but I want to make sure all the scripts using the sa id and password must be changed too so that the scripts are still working, my question is how can I find out the user sa holding on what scripts. Is there a command similar to sp_helprotect or this is the only command be able to do it?

Thanks

 

[JMCraig]

It sounds like what you really need is a way to search through your scripts to find ones that use 'SA' as the login. That is, the place you need to look isn't inside the DB (the 'SA' login is going to have permission to do everything that can be done so that's not going to tell you anything). If you're running your scripts via ISQL, you could use grep (on Unix) or the Search function in the file utility (if you're on Windows or have a GUI on your Unix system) to search for scripts that include 'ISQL'--you could also look for entries that have &quot;-Usa&quot;.

Now, if you're not using ISQL to run the scripts, then you'd want to modify your search strategy accordingly.

A better approach might be to create another user ID that has the sa_role assigned to it (to become the functional 'SA' login), add another login with just the permissions needed to run the scripts, then change all the scripts you can find to use the new login. Finally, lock the SA login itself so it can't be used (a good practice anyway for improved security). When a script fails, you can change it to use the new login.

HTH,

John
Alpha-G Consulting, LLC
nsjmcraig@netscape.net

 

[babeo]

Thanks John

I think it is the only way.