Copy Permissions

Hi,
I've copied tables and views from one SQL Server to another, but the
permissions for each user/role are not copied.
How come.
Thanks,
B.Bart
--
EXEC databasename..sp_helpuser 'username'
SELECT OBJECT_NAME(id),*
FROM databasename..syspermissions
WHERE grantee=USER_ID('username')
If you use BACKUP/RESTORE command to copy your tables/views so please
search on internet for "sp_help_revlogin" two stored procedures provided by
MS to copy logins beween SQL Servers
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:OtwLA6myFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've copied tables and views from one SQL Server to another, but the
> permissions for each user/role are not copied.
> How come.
> Thanks,
> B.
>

Copy Permissions

Hi,
I've copied tables and views from one SQL Server to another, but the
permissions for each user/role are not copied.
How come.
Thanks,
B.
Bart
EXEC databasename..sp_helpuser 'username'
SELECT OBJECT_NAME(id),*
FROM databasename..syspermissions
WHERE grantee=USER_ID('username')
If you use BACKUP/RESTORE command to copy your tables/views so please
search on internet for "sp_help_revlogin" two stored procedures provided by
MS to copy logins beween SQL Servers
"Bart Steur" <solnews@.xs4all.nl> wrote in message
news:OtwLA6myFHA.460@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I've copied tables and views from one SQL Server to another, but the
> permissions for each user/role are not copied.
> How come.
> Thanks,
> B.
>

copy DTS from a user to another

Hi,
I have some local packages that are owned by the old sqlserver dba.I'm not
able to see them, nor to save them with my user (i have granted the system
administrator role).
I want to see these packages and move them to a new user.
How can I do this?
Thanks in advance,If you are trying to change the owner of these packages, then you can use
the undocumented procedure, sp_reassign_dtspackageowner.
For details refer to: http://www.sqldts.com/?212
http://support.microsoft.com/?kbid=247052
Anith|||Hi,
I saved the DTS package as my user but I'm still not able to edit them.
Is it possible that there's a password?
Thanks,
Tarek
"Anith Sen" wrote:

> If you are trying to change the owner of these packages, then you can use
> the undocumented procedure, sp_reassign_dtspackageowner.
> For details refer to: http://www.sqldts.com/?212
> http://support.microsoft.com/?kbid=247052
> --
> Anith
>
>sqlsql

Copy db - lose login mapping

Whenever I copy my production db back to my development server i lose the
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
Thanks
Hi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>

Copy db - lose login mapping

Whenever I copy my production db back to my development server i lose the
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
ThanksHi
http://dimantdatabasesolutions.blogspot.com/2007/04/sql-or-windows-authentication.html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>sqlsql

Copy db - lose login mapping

Whenever I copy my production db back to my development server i lose the
user mapping for my app account and have to drop the db user, then remap the
login to the correct db. Then I have to reassign permissions to the user.
How can I prevent this from happening?
ThanksThat's because the info to tie the user to the login is gone - the
users are orphaned. There is an sp you can use to fix orphaned users.
I am not where I can look it up but I have a SQL script that takes
care of mapping my users to the logins. I am assuming the logins and
users are the same on both systems. You can also check the
knowledgebase and look at the information on moving users and keeping
thier passwords, etc. when they move.
On Fri, 21 Dec 2007 15:05:00 -0800, ken s
<kens@.discussions.microsoft.com> wrote:

>Whenever I copy my production db back to my development server i lose the
>user mapping for my app account and have to drop the db user, then remap th
e
>login to the correct db. Then I have to reassign permissions to the user.
>How can I prevent this from happening?
>Thanks
>|||Hi
http://dimantdatabasesolutions.blog...on.
html
"ken s" <kens@.discussions.microsoft.com> wrote in message
news:4B16A51F-FAD3-4D86-8D5D-1C0A03DBD660@.microsoft.com...
> Whenever I copy my production db back to my development server i lose the
> user mapping for my app account and have to drop the db user, then remap
> the
> login to the correct db. Then I have to reassign permissions to the user.
> How can I prevent this from happening?
> Thanks
>

copy database wizard errorCode=-1073548784 or any solution to "Executed as user: Companynam

I am transfering a database from sql server 2000 to sql server 2005 using copy database wizard.

I have this error coming up error

Error: ERROR : errorCode=-1073548784 description=Executing the query "EXEC dbo.sp_grantdbaccess @.loginame = N'Test\username', @.name_in_db = N'username' " failed with the following error: "The login already has an account under a different user name.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC} StackTrace: at Microsoft.SqlServer.Management.Dts.DtsTransferProvider.ExecuteTransfer() at Microsoft.SqlServer.Management.Smo.Transfer.TransferData() at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()

How do i handle this error...looked online but didn't find right answers...

Please let me know

Hi sureshv,

It seems that it migrates database users. This is not the same as logins.
Logins have access on server level, users on database level.

Perhaps - it may works by using sp_changedbowner 'sa'

CU

tosc

|||

WHEN I AM USING A COPY DATABASE WIZARD HOW AND WHERE DO I NEED TO USE sp_changedbowner 'sa'

AND OTHER QUESTION IS DOES THIS COPY WIZARD EVER WORK PROPERLY...

so i though to use a backup and restore method in order to tansfer 2000 database to 2005 server.....Which worked out well but i have this error coming up when i tried to run my backup for the databse transfered in 2005....

"Executed as user: Companyname\dba. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."

How to overcome this situation..

COPY database wizard error

When I try to copy a SQL Server 2005 database to the same server, the Copy
database wizard always gets an error at the very end. It says "User
'abcuser' does not exist or you don't have permissions". 'abcuser' is a SQL
Server user that we no longer use because it wouldn't work for our
application anymore once we restored into SQL Server 2005 from SQL Server
2000. We can't delete 'abcuser' because its says it is an owner. We can't
change the owner to another user either. The copy process yields a new copy
of the database, BUT we can't see any tables, can't add users, etc. because
it says we don't have premissions. Throughout the COPY database wizard we
used Windows Authentication when asked.
Question: is there a known bug when restoring from SQL Server 2000 in terms
of users? Is there any way to copy this database even though it has this
rogue user?
Thanks!Dean J Garrett wrote:
> When I try to copy a SQL Server 2005 database to the same server, the Copy
> database wizard always gets an error at the very end. It says "User
> 'abcuser' does not exist or you don't have permissions". 'abcuser' is a SQL
> Server user that we no longer use because it wouldn't work for our
> application anymore once we restored into SQL Server 2005 from SQL Server
> 2000. We can't delete 'abcuser' because its says it is an owner. We can't
> change the owner to another user either. The copy process yields a new copy
> of the database, BUT we can't see any tables, can't add users, etc. because
> it says we don't have premissions. Throughout the COPY database wizard we
> used Windows Authentication when asked.
> Question: is there a known bug when restoring from SQL Server 2000 in terms
> of users? Is there any way to copy this database even though it has this
> rogue user?
> Thanks!
>
There's a simple fix for the 'abcuser' problem that started this whole
mess. Go to the original server, look at the syslogins table, and make
note of the "sid" that is assigned to the 'abcuser' login. Create a
login on your new server, named 'abcuser', and assign it the same "sid"
that it had on the original server. This will resolve the original
problem that you had after restoring the database.
Once that's resolved, stop using the Copy Database Wizard. If you want
to clone a database, do a backup/restore, making sure to use the WITH
MOVE option on the restore command to create new data files for the
restored database. Much faster and more straightforward than that
wizard contraption.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Copy database wizard

Hi,
Am using Copy database wizard to copy a database from one server to another server (Both are SQL server 2000). After I specify the sysadmin user name and password for both source as well as destination server, i get the error saying
"The name 'DatabaseName' was not found in the databases collection"
I checked the same database name in sysdatabases and it looks fine.
Now because of this error, am not able to copy any database.
What could be the problem
Thank
YogishOne of the solution I found was to change the database ownership by using SP_CHANGEDBOWNER. I tried this, but it is still the same.
Has anybody faced this kind of situation
Thank
GYKsqlsql

Copy Database Wizard

I want to copy a database that is currently in use. The
DTS package fails if any user is connected. What command
would I issue to block all new connections to the
database? With new connections blocked and existing ones
finished, I should be able to copy the file the way the
wizard configures the package.Check out the ALTER DATABASE command in BOL
Nathan H.O.|||I assume that you are recommending something like:
ALTER DATABASE myDB SET SINGLE_USER
This command locks when the database is already in use.
>--Original Message--
>Check out the ALTER DATABASE command in BOL
>Nathan H.O.
>|||Not if you specify the proper ROLLBACK option.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Dan" <deletethisprefixandthefirstperiod.cathedr@.dshs.wa.gov> wrote in message
news:2aab201c39270$35205650$a601280a@.phx.gbl...
> I assume that you are recommending something like:
> ALTER DATABASE myDB SET SINGLE_USER
> This command locks when the database is already in use.
>
> >--Original Message--
> >Check out the ALTER DATABASE command in BOL
> >
> >Nathan H.O.
> >

Copy database wizard

Hi,
Am using Copy database wizard to copy a database from one server to another
server (Both are SQL server 2000). After I specify the sysadmin user name an
d password for both source as well as destination server, i get the error sa
ying
"The name 'DatabaseName' was not found in the databases collection".
I checked the same database name in sysdatabases and it looks fine.
Now because of this error, am not able to copy any database.
What could be the problem?
Thanks
YogishOne of the solution I found was to change the database ownership by using SP
_CHANGEDBOWNER. I tried this, but it is still the same.
Has anybody faced this kind of situation?
Thanks
GYKsqlsql

Converting User SIDS from SQL 2000 to SQL 2005

WE have a query that we use whenever we transfer a database between servers
that resyns the SIDS in the database to match those in the new installation.
I am trying to convert a databse from 2000 to run on 2005 and the query no
longer works because 2005 does not store the user information in the same wa
y
as 2000. Can someone point me in the right direction to update this query
for 2005. Or, perhaps there is another approach I should be taking?
Here is the query:
/ ****************************************
*****************
* This script is used to synch the SIDs between
* the login and the database user. This should
* be executed after a database is copied to a new
* server and attached to keep the permissions as
* they were on the server where the database originated.
****************************************
******************/
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO
DECLARE @.LoginSID VARBINARY(85)
-- Update the SID for the SEMS user.
SELECT @.LoginSID = sid
FROM master..sysxlogins
WHERE name = 'SEMS'
UPDATE sysusers
SET sid = @.LoginSID
WHERE name = 'SEMS'
-- Update the SID for the RptWriters user.
SELECT @.LoginSID = sid
FROM master..sysxlogins
WHERE name = 'RptWriters'
UPDATE sysusers
SET sid = @.LoginSID
WHERE name = 'RptWriters'
-- Update the SID for the TSEQALS user.
SELECT @.LoginSID = sid
FROM master..sysxlogins
WHERE name = 'TSEQALS'
UPDATE sysusers
SET sid = @.LoginSID
WHERE name = 'TSEQALS'
GO
sp_configure 'allow updates', 0
GO
RECONFIGURE
GO
Thanks,
JohnJohn
I did it by using two stored procedures that MS have provided. I have not
played with it on SQL Server 2005 since I re-created logins on the new
server , however it worth to try.
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
sp_help_revlogin
"John Beschler" <JohnBeschler@.discussions.microsoft.com> wrote in message
news:8F5D93F3-5E94-46C7-ACA1-5B85B907E099@.microsoft.com...
> WE have a query that we use whenever we transfer a database between
> servers
> that resyns the SIDS in the database to match those in the new
> installation.
> I am trying to convert a databse from 2000 to run on 2005 and the query no
> longer works because 2005 does not store the user information in the same
> way
> as 2000. Can someone point me in the right direction to update this query
> for 2005. Or, perhaps there is another approach I should be taking?
> Here is the query:
> / ****************************************
*****************
> * This script is used to synch the SIDs between
> * the login and the database user. This should
> * be executed after a database is copied to a new
> * server and attached to keep the permissions as
> * they were on the server where the database originated.
> ****************************************
******************/
> sp_configure 'allow updates', 1
> GO
> RECONFIGURE WITH OVERRIDE
> GO
> DECLARE @.LoginSID VARBINARY(85)
> -- Update the SID for the SEMS user.
> SELECT @.LoginSID = sid
> FROM master..sysxlogins
> WHERE name = 'SEMS'
> UPDATE sysusers
> SET sid = @.LoginSID
> WHERE name = 'SEMS'
> -- Update the SID for the RptWriters user.
> SELECT @.LoginSID = sid
> FROM master..sysxlogins
> WHERE name = 'RptWriters'
> UPDATE sysusers
> SET sid = @.LoginSID
> WHERE name = 'RptWriters'
> -- Update the SID for the TSEQALS user.
> SELECT @.LoginSID = sid
> FROM master..sysxlogins
> WHERE name = 'TSEQALS'
> UPDATE sysusers
> SET sid = @.LoginSID
> WHERE name = 'TSEQALS'
> GO
> sp_configure 'allow updates', 0
> GO
> RECONFIGURE
> GO
>
> Thanks,
> John
>|||Uri,
Thanks for your suggestion; however, apparently, the sysxlogins table no
longer exists in 2005. That's the point my script was failing as well.
Thanks,
"Uri Dimant" wrote:

> John
> I did it by using two stored procedures that MS have provided. I have not
> played with it on SQL Server 2005 since I re-created logins on the new
> server , however it worth to try.
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> sp_help_revlogin
>
> "John Beschler" <JohnBeschler@.discussions.microsoft.com> wrote in message
> news:8F5D93F3-5E94-46C7-ACA1-5B85B907E099@.microsoft.com...
>
>|||Hi John.
Take a look at this link http://support.microsoft.com/kb/246133/en-us. I
change this script to generate new DDL sintax for SQL Server 2005. You can
get it from
http://solidqualitylearning.com/blo...01/28/1515.aspx
(comments are in Spanish, sorry :-) )
"John Beschler" <JohnBeschler@.discussions.microsoft.com> escribi en el
mensaje news:D002F332-27CD-422A-B26C-BA267F5C77C3@.microsoft.com...[vbcol=seagreen]
> Uri,
> Thanks for your suggestion; however, apparently, the sysxlogins table no
> longer exists in 2005. That's the point my script was failing as well.
> Thanks,
>
> "Uri Dimant" wrote:
>

Converting to SQL Server 2000 from Access

We have an application in Access 2003. The user interface is OK (Forms
created with Access) but we're lacking a "real" database i.e SQL Server 2000
.
Any hints on how to move the database without having to rewrite the user
interface? We could rewrite the Forms in C# but time is not on our side!
--
FolkeThere are no such things as "forms" in SQL Server; this is all handled by
external applications.
http://www.aspfaq.com/
(Reverse address to reply.)
"folke" <folke@.discussions.microsoft.com> wrote in message
news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> We have an application in Access 2003. The user interface is OK (Forms
> created with Access) but we're lacking a "real" database i.e SQL Server
2000.
> Any hints on how to move the database without having to rewrite the user
> interface? We could rewrite the Forms in C# but time is not on our side!
> --
> Folke|||That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>|||You can export the tables to SQL Server, using DTS. You'll have to apply
integrity constraints on your own. Then, alter the Access database to use
linked tables. Ideally, you should make Access use stored procs, instead of
going against the SQL Server tables directly.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"folke" <folke@.discussions.microsoft.com> wrote in message
news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>|||Thanks!
That was what I'm looking for. Step 1 will be to export the tables to SQL
Server and let Access use linked tables!
"Tom Moreau" wrote:

> You can export the tables to SQL Server, using DTS. You'll have to apply
> integrity constraints on your own. Then, alter the Access database to use
> linked tables. Ideally, you should make Access use stored procs, instead
of
> going against the SQL Server tables directly.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
> That's why I'm asking! Can you move the tables to SQL Server and use them
> from Access via some external link? We don't want to rewrite the applicati
on
> just move the tables.
> "Aaron [SQL Server MVP]" wrote:
>
>

Converting to SQL Server 2000 from Access

We have an application in Access 2003. The user interface is OK (Forms
created with Access) but we're lacking a "real" database i.e SQL Server 2000.
Any hints on how to move the database without having to rewrite the user
interface? We could rewrite the Forms in C# but time is not on our side!
Folke
There are no such things as "forms" in SQL Server; this is all handled by
external applications.
http://www.aspfaq.com/
(Reverse address to reply.)
"folke" <folke@.discussions.microsoft.com> wrote in message
news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> We have an application in Access 2003. The user interface is OK (Forms
> created with Access) but we're lacking a "real" database i.e SQL Server
2000.
> Any hints on how to move the database without having to rewrite the user
> interface? We could rewrite the Forms in C# but time is not on our side!
> --
> Folke
|||That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>
|||You can export the tables to SQL Server, using DTS. You'll have to apply
integrity constraints on your own. Then, alter the Access database to use
linked tables. Ideally, you should make Access use stored procs, instead of
going against the SQL Server tables directly.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"folke" <folke@.discussions.microsoft.com> wrote in message
news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>
|||Thanks!
That was what I'm looking for. Step 1 will be to export the tables to SQL
Server and let Access use linked tables!
"Tom Moreau" wrote:

> You can export the tables to SQL Server, using DTS. You'll have to apply
> integrity constraints on your own. Then, alter the Access database to use
> linked tables. Ideally, you should make Access use stored procs, instead of
> going against the SQL Server tables directly.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
> That's why I'm asking! Can you move the tables to SQL Server and use them
> from Access via some external link? We don't want to rewrite the application
> just move the tables.
> "Aaron [SQL Server MVP]" wrote:
>
>

Converting to SQL Server 2000 from Access

We have an application in Access 2003. The user interface is OK (Forms
created with Access) but we're lacking a "real" database i.e SQL Server 2000.
Any hints on how to move the database without having to rewrite the user
interface? We could rewrite the Forms in C# but time is not on our side!
--
FolkeThere are no such things as "forms" in SQL Server; this is all handled by
external applications.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"folke" <folke@.discussions.microsoft.com> wrote in message
news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> We have an application in Access 2003. The user interface is OK (Forms
> created with Access) but we're lacking a "real" database i.e SQL Server
2000.
> Any hints on how to move the database without having to rewrite the user
> interface? We could rewrite the Forms in C# but time is not on our side!
> --
> Folke|||That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:
> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> > We have an application in Access 2003. The user interface is OK (Forms
> > created with Access) but we're lacking a "real" database i.e SQL Server
> 2000.
> > Any hints on how to move the database without having to rewrite the user
> > interface? We could rewrite the Forms in C# but time is not on our side!
> > --
> > Folke
>
>|||You can export the tables to SQL Server, using DTS. You'll have to apply
integrity constraints on your own. Then, alter the Access database to use
linked tables. Ideally, you should make Access use stored procs, instead of
going against the SQL Server tables directly.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"folke" <folke@.discussions.microsoft.com> wrote in message
news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:
> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> > We have an application in Access 2003. The user interface is OK (Forms
> > created with Access) but we're lacking a "real" database i.e SQL Server
> 2000.
> > Any hints on how to move the database without having to rewrite the user
> > interface? We could rewrite the Forms in C# but time is not on our side!
> > --
> > Folke
>
>|||Thanks!
That was what I'm looking for. Step 1 will be to export the tables to SQL
Server and let Access use linked tables!
"Tom Moreau" wrote:
> You can export the tables to SQL Server, using DTS. You'll have to apply
> integrity constraints on your own. Then, alter the Access database to use
> linked tables. Ideally, you should make Access use stored procs, instead of
> going against the SQL Server tables directly.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
> That's why I'm asking! Can you move the tables to SQL Server and use them
> from Access via some external link? We don't want to rewrite the application
> just move the tables.
> "Aaron [SQL Server MVP]" wrote:
> > There are no such things as "forms" in SQL Server; this is all handled by
> > external applications.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "folke" <folke@.discussions.microsoft.com> wrote in message
> > news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> > > We have an application in Access 2003. The user interface is OK (Forms
> > > created with Access) but we're lacking a "real" database i.e SQL Server
> > 2000.
> > > Any hints on how to move the database without having to rewrite the user
> > > interface? We could rewrite the Forms in C# but time is not on our side!
> > > --
> > > Folke
> >
> >
> >
>

Converting to Hex

Hi All,

I'm needing to take a value inputted by a user via html form and convert it to a hex value upon inserting into SQL2000 db. I only need to store the 8 chars after "0x". Is there any T-SQL that can pull this off? CAST or CONVERT? Sorry if this is a silly question and hope I supplied enough info...

Thx,
Mike
Check this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=386406&SiteID=1) for Umachandar's solution (which are always great!)

converting sql 6.5 to access

Hey,
I have a SQL 6.5 db that I need to make accessible to a user for occasional
queries. The SQL server itself is being retired. Can I convert it from SQL
6.5 to Access? How would I do this?
tia,
jj
Hi,
Access can not hold the data if the SQL 6.5 database is more than 1 GB or so
( Not so sure). I recommend you to keep SQL 6.5 or
upgrade it to SQL 2000.
Steps to convert:-
To easily convert all of your SQL tables on a regular basis to Access
tables, you could:
1. Link the SQL tables you will need
2. Build Make Table queries for each table you wish to port to Access
3. Create a code that deletes the records in the local tables and runs the
make table queries to bring in the latest records from
SQL Server.
You can also use SQL 6.5 to replicate tables to move data to Access.
Thanks
Hari
SQL Server MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:%23BEPPS2PFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hey,
> I have a SQL 6.5 db that I need to make accessible to a user for
> occasional queries. The SQL server itself is being retired. Can I convert
> it from SQL 6.5 to Access? How would I do this?
> tia,
> jj
>

converting sql 6.5 to access

Hey,
I have a SQL 6.5 db that I need to make accessible to a user for occasional
queries. The SQL server itself is being retired. Can I convert it from SQL
6.5 to Access? How would I do this?
tia,
jjHi,
Access can not hold the data if the SQL 6.5 database is more than 1 GB or so
( Not so sure). I recommend you to keep SQL 6.5 or
upgrade it to SQL 2000.
Steps to convert:-
To easily convert all of your SQL tables on a regular basis to Access
tables, you could:
1. Link the SQL tables you will need
2. Build Make Table queries for each table you wish to port to Access
3. Create a code that deletes the records in the local tables and runs the
make table queries to bring in the latest records from
SQL Server.
You can also use SQL 6.5 to replicate tables to move data to Access.
Thanks
Hari
SQL Server MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:%23BEPPS2PFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hey,
> I have a SQL 6.5 db that I need to make accessible to a user for
> occasional queries. The SQL server itself is being retired. Can I convert
> it from SQL 6.5 to Access? How would I do this?
> tia,
> jj
>

converting sql 6.5 to access

Hey,
I have a SQL 6.5 db that I need to make accessible to a user for occasional
queries. The SQL server itself is being retired. Can I convert it from SQL
6.5 to Access? How would I do this?
tia,
jjHi,
Access can not hold the data if the SQL 6.5 database is more than 1 GB or so
( Not so sure). I recommend you to keep SQL 6.5 or
upgrade it to SQL 2000.
Steps to convert:-
To easily convert all of your SQL tables on a regular basis to Access
tables, you could:
1. Link the SQL tables you will need
2. Build Make Table queries for each table you wish to port to Access
3. Create a code that deletes the records in the local tables and runs the
make table queries to bring in the latest records from
SQL Server.
You can also use SQL 6.5 to replicate tables to move data to Access.
Thanks
Hari
SQL Server MVP
"jj" <jeff_detoro@.urmc.rochester.edu> wrote in message
news:%23BEPPS2PFHA.1268@.TK2MSFTNGP14.phx.gbl...
> Hey,
> I have a SQL 6.5 db that I need to make accessible to a user for
> occasional queries. The SQL server itself is being retired. Can I convert
> it from SQL 6.5 to Access? How would I do this?
> tia,
> jj
>

converting problems

I have a stored procedure which I user to retur an Email-address from aspnet_Membership (DB-table).

Here is the code:

ALTER PROCEDURE dbo.StoredProcedure2
@.user nvarchar(256)
AS
DECLARE @.id uniqueidentifier
SET @.id = ''
SELECT @.id = UserId from aspnet_Users WHERE UserName = @.user
SELECT Email From aspnet_Membership WHERE UserId = @.id

I use StoredProcedure2 in this way:

Dim cmd As SqlCommand = New SqlCommand("StoredProcedure2", MyConnection)
cmd.CommandType = CommandType.StoredProcedure

'send in UserName as @.User to StoredProcedure2
cmd.Parameters.Add("@.user", SqlDbType.NVarChar).Value = User.Identity.Name.ToString()
'Return EmailAddresse
returnValue = cmd.ExecuteScalar()

Message I get is:

"Conversion failed when converting from a character string to uniqueidentifier"

Please help...

where do you have returnValue dimensioned?

it seems to me the problem is that ExecuteScalar is returning a string and returnValue is set as a different data type.

hth,
mcm

|||Remove this line from your stored procedure:

SET @.id = ''

The error indicates you can't convert an empty string to a uniqueidentifier, as empt string doesn't match uniqueidentifier?format.?A column or local variable of uniqueidentifier data type can be initialized to a value in two ways:

a)?Using the NEWID function.
b) Converting from a string constant in the following form (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f). For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

If you're not sure what's wrong when executing a SQL statement/SP from application, you can run it with proper value in Query Analyzer/Management Studio for t-shooting.