Fix users for SQL 7 thru 2008

  • Comments posted to this topic are about the item Fix users for SQL 7 thru 2008

  • Two things I noticed that I added to the script:

    First, I added brackets around the @Database definition in the cursor. This allows for database names with periods etc.

    Second, I added a check for read only databases when attempting to 'Fix' so the process does not fail.

    Otherwise, good job!

    Flar

  • Thanks Flar, I had already stumbled upon the requirement for the brackets on the database names when I ran this script on one of my older database servers. I know that I should always use the brackets but they drive me crazy :hehe: But I added them anyway.

    I hadn't thought about the read only... I'll look into that. 😎

    MT

  • This is the most wonderful script ever!!!!!!!!!!

  • Hello,

    Nicely done! Unfortunately I'm having a problem executing on a SQL 2005 server. Do you have an updated script I could use?

    I think everyone should have this script including MS!

    Rudy

    Rudy

  • Thanks - nice script.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Yes, I do have an update. I have posted it but so far I have not seen my changes show up on the website. It now handles read only datbases better, and I fixed a couple of errors I found.

    My latest is now posted. If there is still an error in 2005, let me know.

  • Thanks for updating the file. Unfortunately I'm still having issues with the creation of the sp. I've copied and pasted the script and change the ALTER command to CREATE but get the following errors. (I'm executing this on a x64 version of SQL 2005).

    Any ideas on how to fix this would be great.

    Rudy

    Msg 102, Level 15, State 1, Line 3

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 16

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 17

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 17

    Must declare the scalar variable "@DBName".

    Msg 102, Level 15, State 1, Line 18

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 18

    Must declare the scalar variable "@User".

    Msg 102, Level 15, State 1, Line 20

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 20

    Must declare the scalar variable "@DBName".

    Msg 137, Level 15, State 2, Line 21

    Must declare the scalar variable "@Action".

    Msg 137, Level 15, State 2, Line 22

    Must declare the scalar variable "@User".

    Msg 137, Level 15, State 2, Line 24

    Must declare the scalar variable "@DBName".

    Msg 102, Level 15, State 1, Line 25

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 26

    Must declare the scalar variable "@DBName".

    Msg 137, Level 15, State 2, Line 29

    Must declare the scalar variable "@DBName".

    Msg 102, Level 15, State 1, Line 34

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 35

    Must declare the scalar variable "@DBName".

    Msg 102, Level 15, State 1, Line 37

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 38

    Must declare the scalar variable "@Action".

    Msg 137, Level 15, State 2, Line 40

    Must declare the scalar variable "@Action".

    Msg 137, Level 15, State 2, Line 43

    Must declare the scalar variable "@Action".

    Msg 102, Level 15, State 1, Line 50

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 51

    Must declare the scalar variable "@err_flag".

    Msg 102, Level 15, State 1, Line 81

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 1, Line 92

    Must declare the scalar variable "@ParmDefinition".

    Msg 102, Level 15, State 1, Line 94

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 94

    Must declare the scalar variable "@DBName".

    Msg 102, Level 15, State 1, Line 97

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 100

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 105

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 108

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 112

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 114

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 116

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 116

    Must declare the scalar variable "@Database".

    Msg 102, Level 15, State 1, Line 119

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 119

    Must declare the scalar variable "@Database".

    Msg 137, Level 15, State 2, Line 120

    Must declare the scalar variable "@Database".

    Msg 137, Level 15, State 2, Line 121

    Must declare the scalar variable "@DBReadOnly".

    Msg 137, Level 15, State 2, Line 123

    Must declare the scalar variable "@cmd".

    Msg 137, Level 15, State 2, Line 124

    Must declare the scalar variable "@Action".

    Msg 137, Level 15, State 2, Line 124

    Must declare the scalar variable "@cmd".

    Msg 137, Level 15, State 2, Line 126

    Must declare the scalar variable "@cmd".

    Msg 137, Level 15, State 2, Line 127

    Must declare the scalar variable "@cmd".

    Msg 137, Level 15, State 2, Line 129

    Must declare the scalar variable "@User".

    Msg 102, Level 15, State 1, Line 132

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 136

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 141

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 145

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 149

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 151

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 152

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 153

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 155

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 155

    Must declare the scalar variable "@name".

    Msg 102, Level 15, State 1, Line 158

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 158

    Must declare the scalar variable "@Action".

    Msg 137, Level 15, State 2, Line 160

    Must declare the scalar variable "@name".

    Msg 137, Level 15, State 2, Line 161

    Must declare the scalar variable "@name".

    Msg 137, Level 15, State 2, Line 162

    Must declare the scalar variable "@name".

    Msg 137, Level 15, State 2, Line 164

    Must declare the scalar variable "@Action".

    Msg 137, Level 15, State 2, Line 166

    Must declare the scalar variable "@name".

    Msg 137, Level 15, State 2, Line 167

    Must declare the scalar variable "@name".

    Msg 137, Level 15, State 2, Line 169

    Must declare the scalar variable "@Action".

    Msg 137, Level 15, State 2, Line 171

    Must declare the scalar variable "@name".

    Msg 137, Level 15, State 1, Line 173

    Must declare the scalar variable "@login_sid".

    Msg 137, Level 15, State 2, Line 174

    Must declare the scalar variable "@sid".

    Msg 137, Level 15, State 2, Line 176

    Must declare the scalar variable "@name".

    Msg 137, Level 15, State 2, Line 177

    Must declare the scalar variable "@SvrLevel".

    Msg 102, Level 15, State 1, Line 180

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 181

    Must declare the scalar variable "@cmd".

    Msg 102, Level 15, State 1, Line 186

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 189

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 193

    Must declare the scalar variable "@Action".

    Msg 137, Level 15, State 2, Line 195

    Must declare the scalar variable "@name".

    Msg 137, Level 15, State 2, Line 197

    Must declare the scalar variable "@name".

    Msg 137, Level 15, State 2, Line 198

    Must declare the scalar variable "@SvrLevel".

    Msg 102, Level 15, State 1, Line 201

    Incorrect syntax near '?'.

    Msg 137, Level 15, State 2, Line 202

    Must declare the scalar variable "@cmd".

    Msg 102, Level 15, State 1, Line 207

    Incorrect syntax near '?'.

    Msg 102, Level 15, State 1, Line 210

    Incorrect syntax near '?'.

    Rudy

  • I cut and pasted the script from the listing and I got the same errors. I went through and replaced all the white space (tabs, spaces etc.) at the beginnings and endings of all the lines (and sometimes I had to replace the spaces between words) and the procedure compiled after that. Must be some kind of special characer that crept in when posting the script. Sheesh!

    Hope that fixes it for you.

  • Yup that's it. When I copied and pasted into SSMS there seems to be no problem, but when I pasted the script into NotePad++ it all showed up. Once I removed the characters the sp executed properly.

    Now the for the final question. When I execute the script with the FixALL option I get the following error message.

    Msg 259, Level 16, State 1, Line 1

    Ad hoc updates to system catalogs are not allowed.

    Have you seen this before and if so how did you correct it.

    Thanks again for helping me out. This is an EXCELLENT script and should be in the Best of SQL Central.

    Rudy

    Rudy

  • Yes I have seen that. What is happening is the procedure is at the point where it is either trying to fix a lost or orphaned user and it is attempting to do it the SQL 2000 way, which is to update the system tables directly.

    If you look at the line right after the PRINT statement that reads either

    'PRINT '????????Fixing Lost User????:????' + @name' OR 'PRINT '????????Removing Orphan User:????' + @name'

    If you have my latest version of the script, it should read

    'IF @SvrLevel NOT IN ('90','10')'

    Checking to see if this is prior to SQL 2005/2008. If not, then please recopy the script since I have made several changes to the script.

    If this is what you have and it is not working, e-mail me at mtutor@gmail.com

  • D'oh! One more fix. The line:

    IF @SvrLevel NOT IN ('90','10')

    Should be:

    IF @SvrLevel NOT IN ('9.','10')

    I will fix and submit.

    🙂

  • I have found through my own experience that removing users that own objects will cause problems... like you can't get to the objects anymore. I fixed this in the latest version. The script doesn't remove them anymore but it notifies you of how many objects they own.

    This will be available as soon as they publish my update. 🙂

  • Last update. It now has defaults on all parameters. Also you can tell it to change ownership on objects owned by orphaned users.

    Enjoy.

  • This really should be in SQL 2010 🙂 Nicely done!!

    Rudy

Viewing 15 posts - 1 through 15 (of 28 total)

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