Script to Sync Permissions

Question

Post reply

Script to Sync Permissions

Jules Bonnot

SSCertifiable

Points: 5488
More actions
June 7, 2006 at 3:53 am

#114393

SQL 2005
My problem is the user in a database is no longer in sync with the login. The login does not think it is mapped to the user. When i add the user it says it is already in the DB. When i try to delete the user it say i cant because it owns a schema. I cant delete the schema because all the objects in the DB belong to it??
What i think i need is a script to change the schema of every object so i can delete the schema and then the user and then add them back in hopefully syncing them with the login.
Used to be able to fix out of sync logins and users in 2000 by updating system tables but cant do that anymore and the adddition of schemas has made it worse.
Thanks,

www.sql-library.com[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

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

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 1

Most probably you have done a restore. The user is there but not mapped to the current database.

In Users for current database, delete the user in mind. In Security, delete the user in mind.

Now, add the user as usual in Security.

N 56°04'39.16"
E 12°55'05.25"

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 2

cant delete user in current db as it is referenecd by a schema.

need to delete or change schema first which means i must remove the schema from all db objects. Any ideas how to do this.

www.sql-library.com[/url]

Sean Price Grasshopper Points: 12 More actions · Answer 3

In the context of the database you have restored....

sp_change_users_login 'update_one', '', ''

Check out BOL for more info on the sproc

stax68 SSChampion Points: 11711 More actions · Answer 4

Come on 'Jules', you remember this - it's the sysusers/syslogins SID disparity in SQL8. Listen to Sean.

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 5

Yep but cant update sys tables in sql9.

this wopnt work anymore

update

u

set sid = l.sid

from

sysusers u

join

master..syslogins l

on cast(l.name as varbinary) = cast(u.name as varbinary)

and u.uid between 3 and 16383

will have a look at that sp thanks sean.

'Jules'

www.sql-library.com[/url]

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 6

Unfortunatly its a windows NT login that has got out of sync and this sp doesnt update them. the code below coems from that SP. Any Ideas?

-- VALIDATE PARAMS --

-- Can ONLY remap SQL Users to SQL Logins! Should be no need

-- for re-mapping NT logins, and if you try, you'll mess up

-- the user status bits!

if not exists

(select name

from sysusers

where name = @UserNamePattern -- match user name

and issqluser = 1 -- must be sql user

and sid is not NULL

and len(sid) <= 16) -- must not be a sql-user for the database

begin

raiserror(15291,-1,-1,'User',@UserNamePattern)

return (1)

www.sql-library.com[/url]

stax68 SSChampion Points: 11711 More actions · Answer 7

alter authorisation on schema <schname> to <newuser>

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

Jules Bonnot SSCertifiable Points: 5488 More actions · Answer 8

alter schema transfer table to DBO

fixed it in the end.

Thanks,

'Jules'

www.sql-library.com[/url]