Users Databases and Roles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Users Databases and Roles

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2008-10-13 : 20:14:57

Hi All
I found the following on Microsoft
http://support.microsoft.com/kb/246133/
but it does not give me the Dtabases the users are assigned to and the roles the users have.

Is there a way to sript this information out so i get the account
password server role databases assigned and database rolls?

Cheers
Phil

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-13 : 20:26:45

It also maps login to database users. Did you use properly?

stumbling
Posting Yak Master

104 Posts

Posted - 2008-10-13 : 20:32:46

Hi well i think so i am using the Method 2 from this page it creates the user the password sets a default database and server role. But it does not set the databases the user can access and the roles in each database from what i can tell on SQL 2000?
Thanks for your help

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-13 : 20:38:46

You should use Method 1 if transfering from SQL 2000 to SQL 2000

Use Method2 for transfering from SQL 2000 to SQL 2005

stumbling
Posting Yak Master

104 Posts

Posted - 2008-10-13 : 21:08:52

Using option 1 creates the following only
-- Login: phldelme
SET @pwd = CONVERT (varbinary(256), 0x0100042A917F90842BDC07A371E70E90CBE081B36F87C0B7367464CF931C65095EE630F8BAAF430DA0DC86B325A0)
EXEC master..sp_addlogin 'phldelme', @pwd, @sid = 0xA93BE196082A9147A3A939B66A098AAC, @encryptopt = 'skip_encryption'

Using option 2 creates the following
-- Login: phldelme
CREATE LOGIN [phldelme] WITH PASSWORD=0x0100042A917F90842BDC07A371E70E90CBE081B36F87C0B7367464CF931C65095EE630F8BAAF430DA0DC86B325A0 HASHED, CHECK_POLICY=OFF, SID=0xA93BE196082A9147A3A939B66A098AAC
- Login: phldelme
ALTER LOGIN [phldelme] WITH DEFAULT_DATABASE=[Melbourne Cricket Club]
-- Login: phldelme
exec master.dbo.sp_addsrvrolemember @loginame='phldelme', @rolename='sysadmin'

These do not give me the databse access or database roles which is what i am after on my 500 users.
Cheers
Phil

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-13 : 21:24:24

I don't understand what you are trying to do.
If it is in SYSADMIN, why are you worried about database roles?

stumbling
Posting Yak Master

104 Posts

Posted - 2008-10-13 : 22:19:04

That account is just an example we have 500 + accounts of varying levels 490 of them are not sysadmins and just require access to certain DB's and DB roles.
What i am trying to highlight is that the microsoft scripts do not exctract that information and i am trying to find a way to acheive that.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-13 : 22:39:59

After you run above script in destination server.

Run this one please:

Exec sp_Msforeachdb @command1 = 'Exec sp_change_users_login ''Report'''

stumbling
Posting Yak Master

104 Posts

Posted - 2008-10-13 : 23:03:53

Thanks but that returned no users that have stray details.

Mind you i am not testing it on our dev environment just on my local msde with 2 users.

Cheers

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-10-14 : 08:42:31

quote:
Originally posted by sodeep

After you run above script in destination server.

Run this one please:

Exec sp_Msforeachdb @command1 = 'Exec sp_change_users_login ''Report'''

.

If there is any unmatching SID for any users, it will point it out.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources