Copying users from one database to another

how do i copy all the users, roles, permissions etc from on database to
another database?JT
Not easy task. If you have the same structure of the database the best thing
will be restoring the database with a different name
Otherwise, you will have to generate a script for adding users to be mapped
with the logins
sp_grantdbaccess @.login
sp_addrolemember 'db_owner',@.login
"JT" <col@.newsgroup.nospam> wrote in message
news:ec9iL$pNGHA.2884@.TK2MSFTNGP12.phx.gbl...
> how do i copy all the users, roles, permissions etc from on database to
> another database?

Copying Permissions on SPs and Views in Merge Rep

Hi,
I've come across another issue with my merge replication on SQL 2005 SP2.
Every time it recreates the SPs or views (due to snapshot, or changes) it
drops all of the custom persmissions to roles, or users.
I found a "Copy permission" on the tables, but can't find it for views or
procs. Is there a way to automatically have this happen on the SPs and views?
If not, can someone point me in the way of a viable workaround? (such as a
script/CLR to run based on triggers, schedules, whatever)
Ryan S
Sr SQL DBA
1Jn5:12
I script the permissions out for the views and procs and then use a post
snapshot command to apply them.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
> Hi,
> I've come across another issue with my merge replication on SQL 2005 SP2.
> Every time it recreates the SPs or views (due to snapshot, or changes) it
> drops all of the custom persmissions to roles, or users.
> I found a "Copy permission" on the tables, but can't find it for views or
> procs. Is there a way to automatically have this happen on the SPs and
> views?
> If not, can someone point me in the way of a viable workaround? (such as a
> script/CLR to run based on triggers, schedules, whatever)
> --
> Ryan S
> Sr SQL DBA
> 1Jn5:12
|||BTW, here is something I came up with yesterday to do this somewhat
dynamically for the entire server...
if object_id('Tempdb.dbo.##permissions') != 0
Drop table ##permissions
Create Table ##Permissions (Own varchar(270), Ob varchar(270), Grantee
varchar(270), Grantor varchar(270), ProtectType varchar(270), Act
varchar(270), Col varchar(270), DB Varchar(1000))
exec sp_msForeachdb '
use [?]
if ''?'' not in (''master'', ''msdb'', ''tempdb'', ''Model'')
Begin
Declare @.DB nvarchar(1000)
, @.Cmd nvarchar(4000)
set @.DB = ''?''
print ''?''
Insert Into ##Permissions (Own , Ob , Grantee , Grantor , ProtectType ,
Act , Col)
exec sp_helprotect
update ##Permissions set DB = @.DB
where DB is null
Delete from ##Permissions
-- remove permissions for system objects
where ob in (Select sysobjects.Name COLLATE SQL_Latin1_General_CP1_CI_AS
From sysobjects
where OBJECTPROPERTY (sysobjects.id, ''IsSystemTable'') = 1)
End
'
Select 'Use [' + DB + '] ;
if Object_id(''' + Ob + ''') is not null
' + rtrim(ltrim(ProtectType)) + ' ' + rtrim(ltrim(Act)) + ' on [' +
rtrim(ltrim(Ob)) + '] to [' + rtrim(ltrim(Grantee)) + ']'
from ##Permissions p
where ob != '.'
and grantee != 'public' --Do not copy public permissions
and left(grantee, 2) != 'MS' --Remove replication object permissions
drop table ##permissions
Ryan S
Sr SQL DBA
1Jn5:12
"Hilary Cotter" wrote:

> I script the permissions out for the views and procs and then use a post
> snapshot command to apply them.
> --
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Ryan S" <RyanS@.discussions.microsoft.com> wrote in message
> news:67B1D68B-6225-44CF-A88B-4B9DB79734D7@.microsoft.com...
>
>

Copying over permissions on all tables..... ?

We have a situation here were we always loose out permissions and then have to reapply them (and this is a LARGE company! with many group permissions.... Ughhh). I had thought at first that the permissions would be copied with the database when I copied it from one Server the other... not so I found out! So I applied ALL the permissions! Well we took a copy of Server #1 and replaced it on the other Server and all our permissions were gone.

Is there anyway that we can make sure that all the permissions come across. We are moving a database on NT 4 to another one on Win2000 they have different names... is it possible to make sure all the rights come across??

Why do we loose the current permissions (on the Win2000 Server) when we do a complete restore (move over) of the NT 4 systems database to the Win2000 systems database?? Its irritating having to redo all those permissions!

Ponypoor:mad:Your permissions were not gone. They are located on the Master db and you probably copied only the production db.|||I am not quite sure what you mean - We are taking the backup of the whole database (which contains many tables...) and then applying it to the new SQL Server... were do I find the 'master' one... ?|||Hi,

Permissions on a object for a user depends on three tables
a. SYSLOGINS/sysxlogins
b. SYSUSERS
c. syspermissions
d. sysprotects
e. sysobjects

syslogins reside on master database and others reside on user db.

all tables are linked with each other on the basis of suid of sys(x)logins.

once you copy user database from one server to another server all the links are messedup. since suid of one server is different from other.

To RESOLVE this use sp_change_users_login to correct the problem. Read more on BOL

Best of luck|||Thank you very much for the information I will try it out...

Ponypoor|||Try selecting the "generate sql script..." option from enterprise manager and on the "options" tab select "Script database users and roles", "Script SQL Server logins" and "Script object-level permissions".

Some combination of these should at least generate a script that you can apply after restoring the backup.

I'm always leery of restoring a master database from one server to another. No specific reason why but it just seems like it could cause problems.

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.
>

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.
>

Copying "My Reports" between users.

Is there a way to copy a report saved in a users "My Reports" folder to a different users folder? If so what permissions and roles do I need to be in?Using the default settings no user has enough permissions to do this. You would need each user to give you permission or you need to go in as an admin (local RS box admin) and modify the security permissions to give yourself permission.

Copy tables and permissions from one database table to another

Hi,

I want to know how to copy tables and data from one database to antoher database including table permissions. Presently i am using Integrity security services. Is it having any option in Integration services or sqlserver 2005.

Thanks

If I were you I would script all the objects using management Studio and then execute those scripts against your new server/database.

-Jamie

|||

Hi Jamie,

Thanks for your reply. But if you generate script, you are not able to get the data. I need data too. So Scriptng is not usefull in this.If you know any other way please let me know

phani

|||If your database is SQL Server, you can use a Transfer SQL Server Objects task in SSIS to move the objects. You can also use the Import/Export wizard (which generates SSIS packages) to accomplish the same thing.|||

Thread wrote:

Hi Jamie,

Thanks for your reply. But if you generate script, you are not able to get the data. I need data too. So Scriptng is not usefull in this.If you know any other way please let me know

phani

You can use SSIS dataflows to move the data.

The CREATE TABLE scripts can be run from inside the SSIS package using the Execute SQL Task.

Or use the Transfer SQL Server objects Task like John suggests - although I'm not a fan of that task myself.

-Jamie

|||

Jamie Thomson wrote:

Or use the Transfer SQL Server objects Task like John suggests - although I'm not a fan of that task myself.

-Jamie

I've used it fairly successfully for simple, straightforward schema and data transfers. However, judging from the number of posts where people are having issues with it, I may be in the minority.

For more complex scenarios, I wouldn't recommend it.

|||

Hi jamie,

Thanks for your reply.

I've used the SSIS package.

It is working fine with small databases.

But in large database (More than 2 GB) having privileges and rolls on D/b it is not working, generating errors.

May be that was the problem came from the deadlock.

For more information:

I have few tables, few users, Few rolls, Few user defined data types.

Tables are naturally having primary key and foreign keys.

Thanks In Advance

Phani