bcp in users to sysusers table in sql 2005

Question

bcp in users to sysusers table in sql 2005

Waseem Jaleel

SSCommitted

Points: 1706
More actions
July 20, 2008 at 7:33 pm

#122575

I am trying to refresh a db in sql 2005 using a db from a different server. Since I need to retain the users, I did a bcp out of the users and after refreshing the db, I am trying to bcp in the users. This is failing saying adhoc updates on system catlogs not allowed. I already have the logins on my server, so i do not need to run the sp_rev_login proc. Please help me if there is any way to import the users alone, and not the logins (sql 2005)

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

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

Vidhya Sagar SSCrazy Eights Points: 8216 More actions · Answer 1

[font="Verdana"]In SQL Server 2005 you won't be able to update system tables directly. (You can update the tables if you connect to server as DAC)

Since you are not interested in sp_help_revlogin procedure, I suggest you to use "TRANSFER LOGIN TASK" in SSIS to copy the logins from source to destination server.[/font]

Regards..Vidhya Sagar
SQL-Articles

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

You cannot modify the system tables in SQL 2005. Even in SQL 2000 where it was possible it was strongly recommended againast. It's a very quick way of messing up the server.

What I can suggest is that you bcp the users into a normal table, then run a cursor over that table and for each entry run the CREATE USER statement.

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

vidhya sagar (7/20/2008)
(You can update the tables if you connect to server as DAC)

You can read the system tables if you connect with the DAC. Updating them is still not possible.

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

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 4

Gail,

It is a job which is to do this bcp in, so if i use a cursor, and run a create user, I agree that the user can be created. However, there are permissions for each user..how do we add the permission for each user??

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 5

It is a job which is to do this bcp in, so if i use a cursor, and run a create user, I agree that the user can be created. However, there are permissions for each user..how do we add the permission for each user??

I mean, how do I retrieve info about the permission for each user using this alternate table that you (Gail Shaw) has suggested..only if I get info on the permssions can I run the script to grant those required permissions to the user.

Thanks... 🙂

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

Do all the users have the same permissions? If so, create a DB role, grant the permissions to that and assign each user to the role.If not, you'll have to build up the GRANT statements after you run the create user

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Waseem Jaleel (7/20/2008)
I mean, how do I retrieve info about the permission for each user using this alternate table that you (Gail Shaw) has suggested..only if I get info on the permssions can I run the script to grant those required permissions to the user.

Where were you going to get the permissions from when inserting the users straight into sysusers?

Permissions aren't stored in sysusers. Only the definition of the user is stored there.

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

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 8

Thank you Gail..I am not very sure on this because I see around 19 columns in the sysusers table, including a column called role.So, would it be enough that I just create the users as per ur suggestion, and run the fix users script, the user access will b same as before, given that no change happens to the logins on the server. Are you sure that the user would then be able to access the server with the same permissions as before..

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 9

That depends on what your fix user script does.

Oh, for your info. SysUsers ins't a table in SQL 2005. It's a view.

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

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 10

It is just a cursor to run sp_change_users_login proc..would that be enough??? Is there anything else I need to re-gain the permissions etc which are user specific..

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 11

Yes. Assigne them to the required roles or grant them the permissions that they should have.

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

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 12

Would you help me with hints on how this could be done..

Thanks a lot the response that you have given

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 13

Maybe. Are they all members of 1 or more roles? Do all the users have the same permissions? If not, do you have a table or export file somewhere with the permissions they should have?

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

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 14

This is a db refresh job..so I can crate a file to do that, and then read from the same file after the refresh..what are the aspects which I must take into consideration in doing so..