How to sync users and logins in SQL2005 after restore

Does anyone have a script that synchronizes the users and logins in a
database after a restore for SQL 2005?
I used one for SQL 2000, but the domain groups are not synced, and the SQL
users are. So if any one knows how to do this, please post the answer!
The script I used that works half is the following:
[code]
DECLARE @.UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name
--cannot translate sid to existing user=orphaned
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @.UserName
WHILE (@.@.fetch_status = 0)
BEGIN
PRINT @.UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
FETCH NEXT FROM orphanuser_cur INTO @.UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go
[/code]
--
MCDBA 2000
MCSE 2000Hi
BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-0e0641774c04.htm
Take a look at this article
http://blogs.msdn.com/lcris/archive/category/11519.aspx
"G? Brander" <GBrander@.discussions.microsoft.com> wrote in message
news:EAE86B34-3DC8-4027-90C7-1029FC2A34AE@.microsoft.com...
> Does anyone have a script that synchronizes the users and logins in a
> database after a restore for SQL 2005?
> I used one for SQL 2000, but the domain groups are not synced, and the SQL
> users are. So if any one knows how to do this, please post the answer!
> The script I used that works half is the following:
> [code]
> DECLARE @.UserName nvarchar(255)
> DECLARE orphanuser_cur cursor for
> SELECT UserName = name
> FROM sysusers
> WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
> suser_sname(sid) is null
> ORDER BY name
> --cannot translate sid to existing user=orphaned
> OPEN orphanuser_cur
> FETCH NEXT FROM orphanuser_cur INTO @.UserName
> WHILE (@.@.fetch_status = 0)
> BEGIN
> PRINT @.UserName + ' user name being resynced'
> EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
> FETCH NEXT FROM orphanuser_cur INTO @.UserName
> END
> CLOSE orphanuser_cur
> DEALLOCATE orphanuser_cur
> go
> [/code]
> --
> MCDBA 2000
> MCSE 2000
>|||Thanks for your answer, but the first link does tell someting about changing
the db_owner, and I do not see the connection with my problem.
The second link tells me also nothing about my problem.
My problem is:
After a restore from a database to a server that already has the logins, the
users and logins are not in sync. So the id's from the users in the database
are not corresponding anymore. With the script I supplied in my startpost, I
can reconnect the SQL Server logins, but the Windows groups and Windows users
are not reconnected. (BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/11eefa97-a31f-4359-ba5b-e92328224133.htm
[quote]sp_change_users_login cannot be used with Windows logins.[/quote])
MCDBA 2000
MCSE 2000
"Uri Dimant" wrote:
> Hi
> BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-0e0641774c04.htm
>
> Take a look at this article
> http://blogs.msdn.com/lcris/archive/category/11519.aspx
>
> "G? Brander" <GBrander@.discussions.microsoft.com> wrote in message
> news:EAE86B34-3DC8-4027-90C7-1029FC2A34AE@.microsoft.com...
> > Does anyone have a script that synchronizes the users and logins in a
> > database after a restore for SQL 2005?
> > I used one for SQL 2000, but the domain groups are not synced, and the SQL
> > users are. So if any one knows how to do this, please post the answer!
> >
> > The script I used that works half is the following:
> > [code]
> > DECLARE @.UserName nvarchar(255)
> > DECLARE orphanuser_cur cursor for
> > SELECT UserName = name
> > FROM sysusers
> > WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
> > suser_sname(sid) is null
> > ORDER BY name
> >
> > --cannot translate sid to existing user=orphaned
> >
> > OPEN orphanuser_cur
> > FETCH NEXT FROM orphanuser_cur INTO @.UserName
> >
> > WHILE (@.@.fetch_status = 0)
> > BEGIN
> > PRINT @.UserName + ' user name being resynced'
> >
> > EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
> >
> > FETCH NEXT FROM orphanuser_cur INTO @.UserName
> > END
> >
> > CLOSE orphanuser_cur
> > DEALLOCATE orphanuser_cur
> > go
> > [/code]
> >
> > --
> > MCDBA 2000
> > MCSE 2000
> >
>
>|||The database was the Microsoft CRM 3.0 database, and I solved it now, by
scripting the schema's and users, changed the scripts so, that the users
where connected tot the appropiate login's, deleted the schema's and users
and finaly recreated the users and schema's.
There should however be an easier way, because when there are objects owned
by schema's you are not able to delete the schema's.
So if anyone knows the answer, please respond.
--
MCDBA 2000
MCSE 2000
"Uri Dimant" wrote:
> Hi
> BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-0e0641774c04.htm
>
> Take a look at this article
> http://blogs.msdn.com/lcris/archive/category/11519.aspx
>
> "G? Brander" <GBrander@.discussions.microsoft.com> wrote in message
> news:EAE86B34-3DC8-4027-90C7-1029FC2A34AE@.microsoft.com...
> > Does anyone have a script that synchronizes the users and logins in a
> > database after a restore for SQL 2005?
> > I used one for SQL 2000, but the domain groups are not synced, and the SQL
> > users are. So if any one knows how to do this, please post the answer!
> >
> > The script I used that works half is the following:
> > [code]
> > DECLARE @.UserName nvarchar(255)
> > DECLARE orphanuser_cur cursor for
> > SELECT UserName = name
> > FROM sysusers
> > WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
> > suser_sname(sid) is null
> > ORDER BY name
> >
> > --cannot translate sid to existing user=orphaned
> >
> > OPEN orphanuser_cur
> > FETCH NEXT FROM orphanuser_cur INTO @.UserName
> >
> > WHILE (@.@.fetch_status = 0)
> > BEGIN
> > PRINT @.UserName + ' user name being resynced'
> >
> > EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
> >
> > FETCH NEXT FROM orphanuser_cur INTO @.UserName
> > END
> >
> > CLOSE orphanuser_cur
> > DEALLOCATE orphanuser_cur
> > go
> > [/code]
> >
> > --
> > MCDBA 2000
> > MCSE 2000
> >
>
>

How to sync users and logins in SQL2005 after restore

Does anyone have a script that synchronizes the users and logins in a
database after a restore for SQL 2005?
I used one for SQL 2000, but the domain groups are not synced, and the SQL
users are. So if any one knows how to do this, please post the answer!
The script I used that works half is the following:
[code]
DECLARE @.UserName nvarchar(255)
DECLARE orphanuser_cur cursor for
SELECT UserName = name
FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
suser_sname(sid) is null
ORDER BY name
--cannot translate sid to existing user=orphaned
OPEN orphanuser_cur
FETCH NEXT FROM orphanuser_cur INTO @.UserName
WHILE (@.@.fetch_status = 0)
BEGIN
PRINT @.UserName + ' user name being resynced'
EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
FETCH NEXT FROM orphanuser_cur INTO @.UserName
END
CLOSE orphanuser_cur
DEALLOCATE orphanuser_cur
go
[/code]
MCDBA 2000
MCSE 2000Hi
BOL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-
0e0641774c04.htm
Take a look at this article
http://blogs.msdn.com/lcris/archive/category/11519.aspx
"G? Brander" <GBrander@.discussions.microsoft.com> wrote in message
news:EAE86B34-3DC8-4027-90C7-1029FC2A34AE@.microsoft.com...
> Does anyone have a script that synchronizes the users and logins in a
> database after a restore for SQL 2005?
> I used one for SQL 2000, but the domain groups are not synced, and the SQL
> users are. So if any one knows how to do this, please post the answer!
> The script I used that works half is the following:
> [code]
> DECLARE @.UserName nvarchar(255)
> DECLARE orphanuser_cur cursor for
> SELECT UserName = name
> FROM sysusers
> WHERE issqluser = 1 and (sid is not null and sid <> 0x0) and
> suser_sname(sid) is null
> ORDER BY name
> --cannot translate sid to existing user=orphaned
> OPEN orphanuser_cur
> FETCH NEXT FROM orphanuser_cur INTO @.UserName
> WHILE (@.@.fetch_status = 0)
> BEGIN
> PRINT @.UserName + ' user name being resynced'
> EXEC sp_change_users_login 'Update_one', @.UserName, @.UserName
> FETCH NEXT FROM orphanuser_cur INTO @.UserName
> END
> CLOSE orphanuser_cur
> DEALLOCATE orphanuser_cur
> go
> [/code]
> --
> MCDBA 2000
> MCSE 2000
>|||Thanks for your answer, but the first link does tell someting about changing
the db_owner, and I do not see the connection with my problem.
The second link tells me also nothing about my problem.
My problem is:
After a restore from a database to a server that already has the logins, the
users and logins are not in sync. So the id's from the users in the database
are not corresponding anymore. With the script I supplied in my startpost, I
can reconnect the SQL Server logins, but the Windows groups and Windows user
s
are not reconnected. (BOL:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/11eefa97-a31f-4359-ba5b-e923
28224133.htm
[quote]sp_change_users_login cannot be used with Windows logins.[/qu
ote])
MCDBA 2000
MCSE 2000
"Uri Dimant" wrote:

> Hi
> BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9c
d-0e0641774c04.htm
>
> Take a look at this article
> http://blogs.msdn.com/lcris/archive/category/11519.aspx
>
> "G? Brander" <GBrander@.discussions.microsoft.com> wrote in message
> news:EAE86B34-3DC8-4027-90C7-1029FC2A34AE@.microsoft.com...
>
>|||The database was the Microsoft CRM 3.0 database, and I solved it now, by
scripting the schema's and users, changed the scripts so, that the users
where connected tot the appropiate login's, deleted the schema's and users
and finaly recreated the users and schema's.
There should however be an easier way, because when there are objects owned
by schema's you are not able to delete the schema's.
So if anyone knows the answer, please respond.
--
MCDBA 2000
MCSE 2000
"Uri Dimant" wrote:

> Hi
> BOL
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9c
d-0e0641774c04.htm
>
> Take a look at this article
> http://blogs.msdn.com/lcris/archive/category/11519.aspx
>
> "G? Brander" <GBrander@.discussions.microsoft.com> wrote in message
> news:EAE86B34-3DC8-4027-90C7-1029FC2A34AE@.microsoft.com...
>
>

How to supply NT Credential on RS Report Parameter

I have a Filter Dropdown parameter which calls Storeprocedure DataSet
accepts NT Domain and Username as parameter. How will i supply this
parameter to make it work. Right now i hardcode values and it works fine.
In Dot.net Program we can set Identity Impersonate to true and then use
User.Identity Security Principals . How do i use on Reporting Services.
Any idea would be great!I figured it out.. User.UserID ( User running the report)
"RAGHAVAN JAYARAMAN" wrote:
> I have a Filter Dropdown parameter which calls Storeprocedure DataSet
> accepts NT Domain and Username as parameter. How will i supply this
> parameter to make it work. Right now i hardcode values and it works fine.
> In Dot.net Program we can set Identity Impersonate to true and then use
> User.Identity Security Principals . How do i use on Reporting Services.
> Any idea would be great!
>|||I am having the same problem.
Where did you place this code? In the Parameter field itself? Inside of
the report code tab?
Thanks,
msflinx
"RAGHAVAN JAYARAMAN" wrote:
> I figured it out.. User.UserID ( User running the report)
> "RAGHAVAN JAYARAMAN" wrote:
> > I have a Filter Dropdown parameter which calls Storeprocedure DataSet
> > accepts NT Domain and Username as parameter. How will i supply this
> > parameter to make it work. Right now i hardcode values and it works fine.
> >
> > In Dot.net Program we can set Identity Impersonate to true and then use
> > User.Identity Security Principals . How do i use on Reporting Services.
> >
> > Any idea would be great!
> >|||Query parameters do not have to map to a report parameter. RS automatically
creates matching report parameters but you can instead map the query
parameter to an expression. In the dataset tab click on the ..., parameters
tab and map to an expression. When the expression builder comes up, expand
the global variable and set it to the User!UserID global variable. Note that
this has domain\username so if you need just one or the other you need to
strip off the part you don't want.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"msflinx" <msflinx@.discussions.microsoft.com> wrote in message
news:133E55D5-265D-4AAB-A703-B59EC82C3ABB@.microsoft.com...
>I am having the same problem.
> Where did you place this code? In the Parameter field itself? Inside of
> the report code tab?
> Thanks,
> msflinx
> "RAGHAVAN JAYARAMAN" wrote:
>> I figured it out.. User.UserID ( User running the report)
>> "RAGHAVAN JAYARAMAN" wrote:
>> > I have a Filter Dropdown parameter which calls Storeprocedure DataSet
>> > accepts NT Domain and Username as parameter. How will i supply this
>> > parameter to make it work. Right now i hardcode values and it works
>> > fine.
>> >
>> > In Dot.net Program we can set Identity Impersonate to true and then use
>> > User.Identity Security Principals . How do i use on Reporting Services.
>> >
>> > Any idea would be great!
>> >