Hi,
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex co
de for the assembly
instead of the file reference. Then also script your objects created from th
at assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl.
.
> Hi,
> We have recently deployed our application into a parallel production envir
onment which will
> eventually become the main production environment. We have had to make som
e changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production.
Now we are ready to
> deploy these stored procedures to our new production environment. Unfortun
ately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to
keep the production
> data.
> Is there a way that we can copy the stored procedures only from our develo
pment server to our
> production server? They're CLR stored procedures, so we can't script them
out either.
> Thanks
Showing posts with label environment. Show all posts
Showing posts with label environment. Show all posts
Thursday, March 22, 2012
Copying stored procedures to another database
Hi,
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex code for the assembly
instead of the file reference. Then also script your objects created from that assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have recently deployed our application into a parallel production environment which will
> eventually become the main production environment. We have had to make some changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production. Now we are ready to
> deploy these stored procedures to our new production environment. Unfortunately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to keep the production
> data.
> Is there a way that we can copy the stored procedures only from our development server to our
> production server? They're CLR stored procedures, so we can't script them out either.
> Thanks
We have recently deployed our application into a parallel production
environment which will eventually become the main production environment. We
have had to make some changes to a number of CLR (VB) stored procedure,
which we've deployed and tested in production. Now we are ready to deploy
these stored procedures to our new production environment. Unfortunately, we
can't connect deploy remotely, and we can't perform a backup restore because
we need to keep the production data.
Is there a way that we can copy the stored procedures only from our
development server to our production server? They're CLR stored procedures,
so we can't script them out either.
Thanks> They're CLR stored procedures, so we can't script them out either.
Why now? Script the assembly, which will in your script file have the hex code for the assembly
instead of the file reference. Then also script your objects created from that assembly...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Rob" <r_miller@.ozemail.com.au> wrote in message news:u042u2VQIHA.5288@.TK2MSFTNGP04.phx.gbl...
> Hi,
> We have recently deployed our application into a parallel production environment which will
> eventually become the main production environment. We have had to make some changes to a number of
> CLR (VB) stored procedure, which we've deployed and tested in production. Now we are ready to
> deploy these stored procedures to our new production environment. Unfortunately, we can't connect
> deploy remotely, and we can't perform a backup restore because we need to keep the production
> data.
> Is there a way that we can copy the stored procedures only from our development server to our
> production server? They're CLR stored procedures, so we can't script them out either.
> Thanks
Monday, March 19, 2012
Copying encrypted stored procedures.
I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.
HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
http://www.sqlerver2005.de
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.
HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
http://www.sqlerver2005.de
Copying encrypted stored procedures.
I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.(nickwilson.nick@.gmail.com) writes:
> I have several stored procedures, created in a development environment,
> that I need to move to a 'QA' environment, and then in turn, to various
> production environments.
> When I move these stored procedures, I would like to encrypt them,
> using the 'WITH ENCRYPTION' clause.
> My question is, how do I copy these stored procedures from development
> to their target SQL server environment in an encrypted state?
The answer is that you don't. I think SQL 7 had a function for this, but
it was discontinued with SQL 2000.
> Up until now, we have been moving them by generating an SQL script and
> then executing that script on the target server. I have tried this
> using a script with 'WITH ENCRYPTION' specified within it, but it
> doesn't appear to work when I try and execute that script on the target
> server.
What "doesn't appear to work" mean?
You would have to write a tool that reads the script and shifts in
WITH ENCRYPTION before AS. (Assuming that the number of procedures are
too many to make manual editing an option.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Copying encrypted stored procedures.
I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
--
http://www.sqlerver2005.de
--
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
--
http://www.sqlerver2005.de
--
Copying encrypted stored procedures.
I have several stored procedures, created in a development environment,
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
http://www.sqlerver2005.de
--
that I need to move to a 'QA' environment, and then in turn, to various
production environments.
When I move these stored procedures, I would like to encrypt them,
using the 'WITH ENCRYPTION' clause.
My question is, how do I copy these stored procedures from development
to their target SQL server environment in an encrypted state?
Up until now, we have been moving them by generating an SQL script and
then executing that script on the target server. I have tried this
using a script with 'WITH ENCRYPTION' specified within it, but it
doesn't appear to work when I try and execute that script on the target
server.
Any advice would be greatly appreciated.
Nick.HI,
you either need the sources for the procedure or if not accessible or
recoverable you can to to decrypt the procedure text (which isn=B4t
actually hard and can be found in tons of articles on the internet).
HTH; Jens Suessmeyer.
http://www.sqlerver2005.de
--
Copying db from prod to dev without disturbing permissions
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db level
or at the instance level? Thanks.
Do a backup and restore. Then, make sure to run sp_change_users_login to
ensure you align your users and logins.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db
level
or at the instance level? Thanks.
|||Or using two stored procedures provided by MS to transfer logins withb their
original SID.
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
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OGY7V2HQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Do a backup and restore. Then, make sure to run sp_change_users_login to
> ensure you align your users and logins.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "sfhank" <sfhank@.discussions.microsoft.com> wrote in message
> news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
> What's the best way to refresh a dev environment from production without
> disturbing the permissions on the dev box either at the table level, db
> level
> or at the instance level? Thanks.
>
disturbing the permissions on the dev box either at the table level, db level
or at the instance level? Thanks.
Do a backup and restore. Then, make sure to run sp_change_users_login to
ensure you align your users and logins.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db
level
or at the instance level? Thanks.
|||Or using two stored procedures provided by MS to transfer logins withb their
original SID.
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
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OGY7V2HQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Do a backup and restore. Then, make sure to run sp_change_users_login to
> ensure you align your users and logins.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "sfhank" <sfhank@.discussions.microsoft.com> wrote in message
> news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
> What's the best way to refresh a dev environment from production without
> disturbing the permissions on the dev box either at the table level, db
> level
> or at the instance level? Thanks.
>
Copying db from prod to dev without disturbing permissions
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db leve
l
or at the instance level? Thanks.Do a backup and restore. Then, make sure to run sp_change_users_login to
ensure you align your users and logins.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db
level
or at the instance level? Thanks.|||Or using two stored procedures provided by MS to transfer logins withb their
original SID.
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
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OGY7V2HQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Do a backup and restore. Then, make sure to run sp_change_users_login to
> ensure you align your users and logins.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "sfhank" <sfhank@.discussions.microsoft.com> wrote in message
> news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
> What's the best way to refresh a dev environment from production without
> disturbing the permissions on the dev box either at the table level, db
> level
> or at the instance level? Thanks.
>
disturbing the permissions on the dev box either at the table level, db leve
l
or at the instance level? Thanks.Do a backup and restore. Then, make sure to run sp_change_users_login to
ensure you align your users and logins.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"sfhank" <sfhank@.discussions.microsoft.com> wrote in message
news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
What's the best way to refresh a dev environment from production without
disturbing the permissions on the dev box either at the table level, db
level
or at the instance level? Thanks.|||Or using two stored procedures provided by MS to transfer logins withb their
original SID.
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
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OGY7V2HQFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Do a backup and restore. Then, make sure to run sp_change_users_login to
> ensure you align your users and logins.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> .
> "sfhank" <sfhank@.discussions.microsoft.com> wrote in message
> news:6B393D51-C260-476F-9FA2-61B731F8BF57@.microsoft.com...
> What's the best way to refresh a dev environment from production without
> disturbing the permissions on the dev box either at the table level, db
> level
> or at the instance level? Thanks.
>
Subscribe to:
Posts (Atom)