I've built a development server for my SQL 2005 production
environment. I've restore the databases from a backup, and I want to
copy the users from my production to development machine.
How can I do this?
TIA
Rob
Hi Everybody,
I am working on SQL 7.0/2000. I have given lot of permissions to the user 'duser1'. The permissions like select,etc..,create... Now I want to give the same permissions(what I have given to 'duser1') to the other user called 'duser2'. Right now I hvn't created any Database Roles or Server Roles.
Do we have any easy method to copy the permissions of one user to the other user?, like script generation or any method. If anybody knows that please guide me.
tks in advance,
Sam
I must extract rows (with criteria) from a table with a few millions of rows in MS SQL 2000
Is there a way to COPY a table ?
if so is there a way to COPY it in a more selective way ?
something like :
COPY TABLE users AS usersCopy WHERE UserId > 500
thank you
Hello all,I am looking for the script, which I believe exists already.I need tobe able to populate the script for security of one database andapply it to another database, even if it is located on another server:1. All logins which not exist have to be created and which existsignored including the NT accounts2. Users same as the old database + the existing ones stay in database3. Passwords for the new logins.4. All permissions/grants on all objects for the users that exists(usually it's the case) and ignore those that don't.I have script which does some of it, but it's not perfect, so everytime there are some errors.Please let me know, if you need me to email script that I have. It'spretty long so I cannot just post it in here.Thank you in advance.
View 3 Replies View Related
I have a SQL Server 2005 evaluation that has already been installed and setup on a server. I believe it originally had a 180 day eval. There have been numerous databases and users added as well as maintenance plan created...
The eval was put on the machine as an interim solution while waiting for paper work and order processiing things to happen. All the paperwork and ordering... have been completed and I now have the real SQL Server 2005 Standard Edition license key ....
I am very new to SQL Server and need to determine ...
1. Can I update the eval copy to become permanently licensed?
2. Would I want to upgrade the eval to permanent? Will I lose any capabilities by keeping the current eval setup?
If it is reasonable to keep the eval setup
3. How do I go about entering the license key to make it permanent?
If it is necessary to install the new Standard Edition...
4. How do I install it while maintaining the already defined databases, data, users, maintenance plan...
Thanks in advance for any and all help.
Chris
I have read the previous threads on the bugs with this task mainly: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1438968&SiteID=1 . These are great posts that helpmed me avoid wasting time. I haven't seen one yet that addresses copying an entire database including the sql server logins.
I would like to import the ENTIRE database from one (2005) server to another(2005) using the SSIS Transfer SQL Object task (not just sprocs,tables,views and functions). I have figured out how to pull the tables,views,sprocs and functions ... by using an execute sql task to drop these objects. But I cannot get this to work for users since the user dbo cannot be dropped and guest can only be disabled. I am creating a new database (this is the database where the sql objects will be copied to) via management studio to test this. There has to ba a way to get this working ... Microsoft must have published some sort of KB article on this task or a Script Task using SMO object calls. If need be I can drop the entire database on the target machine and have SSIS recreate it.
The only reason I'm willing to take a risk with SSIS rather than backup and restore is because of time constraints (I assume the SSIS task is faster) and backup storage administration.
declare @name varchar(200)
declare @object varchar(200)
DECLARE object_cursor CURSOR READ_ONLY FORWARD_ONLY FOR
select table_name,table_type from INFORMATION_SCHEMA.TABLES
union
Select name,'SPROC' table_type from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'
union
select name,'FN' from sys.objects where type_desc like '%FUNCTION'
OPEN object_cursor
FETCH NEXT FROM object_cursor INTO @name,@object
IF @@FETCH_STATUS <> 0
PRINT ' <<None>>'
WHILE @@FETCH_STATUS = 0
BEGIN
if @object = 'BASE TABLE'
begin
exec ('drop table ' + @name)
end
else if @object = 'VIEW'
begin
exec ('drop view ' + @name)
end
else if @object = 'SPROC'
begin
exec ('drop procedure ' + @name)
end
else if @object = 'FN'
begin
exec ('drop function ' + @name)
end
FETCH NEXT FROM object_cursor INTO @name,@object
END
CLOSE object_cursor
DEALLOCATE object_cursor
I am restoring a production copy of db XYZ to the development server db XYZ. I want to retain the original users on the development server for db XYZ. How do I create a script that will reset the users on the development server with the same permissions and access they had before the production copy was restored on development? I have searched and searched and cannot find the answer. I can use a third party tool - Embarcadero - to script out the create and update stmts but it is not 100% correct. I end up having to do alot of manual work.
any assistance would be greatly appreciated.
thanks
In SQL Server 2000 we had a view that would show the user credentials and the password hash. The reason we need this is that we use SQL Server authentication on the database. To test users, we have a login with little access, and it should be able to see the view and compare the password supplied against what is in the database, and then let the code handle a graceful exit if the password is invalid. I am trying to do this with SQL Server 2005, and I am running into trouble. I am trying to do this with a function, since there I can set the EXECUTE AS clause (in theory) and leverage the privlidges of a specific user in the database. Here is an example function:
CREATE FUNCTION check_acct.fn_allusers (@test int)
RETURNS @users table (username varchar(50), passwd varbinary(256))
WITH EXECUTE AS caller
AS
BEGIN
INSERT @users
select name, password_hash from sys.sql_logins
RETURN
END
GO
GRANT SELECT ON fn_allusers TO user_acct
I cannot get to all rows in the sys.sql_logins table unless I first:
GRANT VIEW ANY DEFINITION TO CHECK_ACCT
then when I call this function from CHECK_ACCT, I am able to see the data. If I change the WITH EXECUTE AS 'CHECK_ACCT' and execute thsi function from USER_ACCT, I do not get the same results. Do functions not inherit VIEW DEFINITION proivlidges? Any suggestions on how to do this but limit access to these secure objects to this one function?
Hi, i just need some help?? just some info?is it TRUE that SQL Server 2005 Express Edition (free) is capable ONLY of SINGLE user?i mean if i have a data driven page, if someone is using or editing the data in my page, does no one can access my data base simultaneously??i hope you could enlighten my mind, thanks
View 2 Replies View RelatedDoes SQL Server 2005 Workgroup Edition have a limit to the number of user logins I can make?
View 1 Replies View RelatedI am sure that I am missing something which is why I am in need of the communities assistance...
I have installed SQL Server 2005 Express Edition. I can log in with the Windows Authenication and the SQL Server Authentication using the 'sa' account. This is all good...
What I want to be able to do is add 1 to 3 more users so they can gain access to the database I have created to run queries and add data. This is the part I am confused about. How do I get additional users added??
Thanks in advance for your time and assistance!
I need to set up 1 new user in SQL Server 2005 to be able to read specific tables in a db (db1).
The user will connect from MS access using odbc links (SQL Native client ot SQL Server driver)
I've tried to set up one and once logged on from the user workstation, I can only see sys. tables and INFORMATION_SCHEMA tables.
None of the required db1 tables appear.
under Security/Logins I've created User1:
SQL Server auth. with password
default db = db1
server_roles = none
user mapping = map, db1, user1,dbo
securables = none
status = grant, enabled
on the access db, the odbc link was set up with default db = db1
Why can't I see any of the db1 tables?
How can I restrict access to the sys. tables?
Thank you
We are running SQL Server 2005 in a Windows 2003 domain and I have a situation where some of my users are unable to connect to the SQL Server unless they are a member of the sysadmin group. Any attempts by these users to login result in a login failed,
Error: 18456, Severity: 14, State: 11
Which indicates that it is a valid user who does not have access to this SQL Server.
I have been able to narrow the failures down to the following situation:
Create a user, TestUser1, as a member of 1 domain local group TestGroup1
Give TestGroup1 access to SQL (standard public access to master)
All good. Login succeeds.
Add TestUser1 to another domain local group TestGroup2
Attempt to login to SQL Server -> login failed.
Add the user explicitly -> login failed
Add one of the groups to sysadmin -> login succeeds
It seems that as long as the user is a member of more than one AD group, and none of those groups is a member of the sysadmin server role then the user is unable to login. Obviously having all of the users as sysadmin isn't a workable solution, has anyone seen this issue before?
I have been able to replicate a similar situation in our test domain, but in that case the issue is resolved by adding the users explicitly to SQL Server (still not an ideal solution).
Interestingly, if I run the same test in our test domain but use global groups, it works. But unfortunately the network admin tells me the groups must stay as local.
Any help would be greatly appreciated.
Regards,
Daniel Watkins
Hi All,
I have just migrated couple of test databases
from SQL Server 2000 to 2005 (side by side).
I also migrated the server level logins using SSIS transfer logins task, available on SSIS 'Transfer logins', I selected all the databases that I have migrated so that I have all database users account in server logins (to avoid orphaned users); but I don't have all the database users in server logins, also the sys.sysusers doesn't have the database users, that I have moved to SQL 2005. Can anybody help?
Also do I need to back up the SQL Server 2000 database and restore it on SQL 2005? What impact this operation can have?
Thanks for your time..
Bidyut
For SQL Server 2000 we have a user login mapped to msdb with database role membership of db_datareader and public checked. This seems to allow the developers to view the Management Activity monitor. For SQL Server 2005 the same mapping is in place but the developers cannot view the Management Activity monitor. Developers are NOT granted the sysadmin role, and should not have that role.
What permissions need to be set for SQL Server 2005 to allow users to view the Management Activity monitor? They should not be allowed to take actions on the activities.
Hi dear devs, admins or users!
Two weeks ago I had a problem with transfering databases from v8 to v9, I placed the question here (original message attached), but I received not the answer which I need. I try to transfer databases with the CDW ("Copy Database Wizard"), which should run through 5 action steps. The process stops after the 'action 2', the step "Create Package" failed allways. The error message which I receive is "Library not registered". I was very surprised, because two weeks ago the process stoped after the action 4 (4/5) while the step "Execute SQL Server Agent Job". I changed nothing on my system or databases. The only thing I can remember is the "Microsoft Update" downloaded and installed a new patch, but it wasn't a SQL Server 2005 update or something with databases.
I have now two questions. What means the error "Library not registered" while the CDW process and what could be the reason for the error in 'action 5' "Execute SQL Server Agent Job" while the CDW process? **(CDW = "Copy Database Wizard")
[original message from 2006-10-17]
I have following problem with the "SQL Server 2005 Developer Edition". I am in a small network and try to copy a database from a version 8.0 to a version 9.0 "SQL Server" (2 computers). I use the "Copy Database Wizard" and the process did 4 from 5 actions correctly, the last action with the name "Execute SQL Server Agent Job" fails allways. My "SQL Server Agent" process is started and runs well. In real I don't need the "SQL Server Agent" in any way in my database, so I don't understand why this process generates this error. I try to copy this old "Portal" "ASP.NET 1.1" database to my new "SQL Server 2005". Can someone help me?
Thanks
Arzu Bazman · «US DEV United Systems»
Hello all,
I need to copy a database from our production server to my local development machine.
This was easy in SQL 2000, but I'm stumped for SQL 2005. In order to use Management Studio's Database/Tasks/Copy Database feature, I must have SysAdmin rights on not only my local machine, but also on the production server machine. Because there are other important databases on that machine, they won't give me sa rights on the entire production machine.
Someone suggested a two step process:
1. I first script the database
2. Then I use Import/Export Wizard to copy the data.
For scripting, I see that in Management Studio, I can use either Database/Script As, or, I can use Database/Tasks/Generate Scripts. Which is more appropriate? They seem to be very different.
Copying a database seems like a common need. How do other members here do it?
Thanks.
Any thoughts or ideas will be appreciated.
Tritim
I have been struggling with this for a while and cannot get it right. I have read countless articles on the internet as well as MSDN documentation about SQL Server 2005 and no success so far.
What I want is a database with multiple SCHEMAS, DATABASE ROLES and USERS in order to have a finer grade of security to access the various objects.
My schemas are (in order to simplify the situation and provide an example of the setup):
- [dbo] the standard DBO schema which is always default. Contains public objects.
- [com] a special module integrated into the system
- [ofc] contains objects used by back-office only
- [aud] contains objects used for auditing, etc.
Each of these schemas has their fair share of tables, views, functions and stored procedures which have been created appropriately (i.e. CREATE TABLE [ofc].[Addresses]), in other words prefixed by the name of the schema to which they belong.
Then I have created various database roles (don't confuse them with SQL2005 application roles) as follows:
- PublicRole mostly used for viewing, no data alterations
- WorkerRole used by front end processes that need write access to data in [dbo] and [aud]
- OfficeRole used by back-office for dealing with backoffice data (basically [ofc], [aud] stuff)
- AdminRole used by application administrator
Each of these database roles has been defined with owner 'dbo', none of them own any schemas. And last but not least to each of these roles I have selected the above named schemas (dbo, ofc, aud) as securables and for each of those securables schemas I have then given the correct set of GRANT/DENY on the Alter, Control, Delete, Execute, Insert, References, Select, Update, Take ownership and View definition.
As an example, the PublicRole role has been given the following permissions (Y=GRANT, N=DENY):
Table #1 of Application Permissions [dbo] [com] [ofc] [aud]Alter N N N N
Control N N N N Delete N N N N Execute Y Y N Y Insert N N N Y References Y Y N Y Select Y Y N Y Update N N N Y Take ownership N N N N View definition N N N N
And I have the following minimum set of database users defined (the server login has the same name):
- upublic, assigned to PublicRole
- uworker, assigned to WorkerRole
- uoffice, assigned to OfficeRole
- uadmin, assigned to AdminRole
As I understand when I assign these users to a particular custom Database Role, the users inherit the permissions granted to THAT role thus liberating me from having to assign the permissions to each and every user account on the same role.
What I expected was that when I logged in to the database with the upublic user account I would at least be able to view (SELECT at least) ALL the objects with the exception of those in th e[ofc] schema. Unfortunately the account is not able to access ANYTHING at all, I get an error like
"The SELECT permission has been denied on object XYZ, database DB, schema 'dbo'"
So, when I use Management Studio to look at the properties I selected the PublicRole and it showed the upublic user as a member of the role (Members of this role). So far so good.
Then when I switch to the Securables page for this role it shows all the schemas I defined and for each of them the same list shown in Table #1 except it has two lines for each permission, the first for Grantor dbo and the second for Grantor upublic. The first has the same permissions I assigned to the role (as shown on Table #1) but the 2nd does not show a checkmark on either GRANT or DENY!!! For example ([x] = checked, [ ] unchecked):
Table #2 Explicit permissions for Securable
Permission Grantor Grant Grant-with Deny
Select dbo [x] [ ] [ ] Select upublic [ ] [ ] [ ]
What am I doing wrong here? Apparently I then have to repeat the whole grant/deny for EACH and EVERY USER I define when the idea was that I would grant/deny on the database role and every member of that role would inherit those permissions automatically.
I have a database called 'DB1' in SQL Server 2000. I want to create the same database in SQL Server 2005 Express including the original data in tables.
How would I do that? I cannot find any option to do this upgrade in SQL Server Management Studio.
Hi,
how can I import (restore) a backup of SQL Server 2000 database to SQL Server 2005 Express?
Thank you all
Hello All,
I have a table in SQL Server Enterprise V8 that i want copied in SQL Server Enterprise 2005. I first generated the script out of V8 and then i tried using the query function in 2005 to execute it. It does does not work. Do i have to complete re-recreate the table in 2005?
-Kagome
Hi all,i am completely new to SQL Server. So how can I do that? WithIntegration Services?Thanks for your helpDaniel
View 1 Replies View RelatedTwo Windows 2003 server,one with SQL 2005 server,another with SQL Express.Is it possible to copy databases from SQL 2005 to SQL Express?Thanks.
View 6 Replies View RelatedI just upgraded to SQLServer 2005 Standard. I do development in adatabase on one server. When a solution is completely tested, I moveit to the operational database on a different server. With SQLServer2000, I could just open the .adp file and do File->Get ExternalData->Import to move the objects. That does not work with 2005.What is the recommended method for moving objects from one database toanother? Obviously, the objects that changed will not be the sameevery time.Thanks!Jer
View 2 Replies View RelatedI'm facing problem to copy database of SQL Server 2005 from one pc to another pc. how can i solve the problem
View 1 Replies View RelatedI am racking my brain on this one. I am migrating Oracle data to SQL Server and would like an SSIS package that will copy the oracle data from every table to SQL server. I already have the schema set up in SQL and a straight copy of a specified table works fine between the OLEDB source and OLEDB destination. However, when I set up a loop which sets a global variable for the table names, which are identical on both dbs, and try to set the OLEDB Source and Destination OpenRowsetVariable to the global variable, the process fails with these errors with all validation turned off:
[OLE DB Source [18]] Warning: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
[OLE DB Source [18]] Error: Column "ID" cannot be found at the datasource.
[DTS.Pipeline] Error: component "OLE DB Source" (18) failed the pre-execute phase and returned error code 0xC0202005.
To sum up, I want to set the tables dynamically and have them infer the mappings. The names of the columns are already the exact same on each schema, and the data types are mapped accordingly. I have already verified that the names of the tables have been formatted correctly for each database. If I can't do it this way then how?
Hi guys,
I am trying to copy an entire SQL SERVER 2000 Database from the SERVER to any place in my laptop in order to run some tests without being connected to the net and the real DB.
I am using the "Copy Database" function in Microsoft SQL Server Management Studio, but getting the Error described below:
I will appreciate any help.
Thanks in advance,
Aldo.
Failed to connect to server MOSHE-DELL. (Copy Database Wizard)
Program Location: at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
at Microsoft.SqlServer.Management.CopyDatabaseWizard.DestinationServerPage.OnLeavePage(LeavePageEventArgs le)
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476
Error Number: 2
Severity: 20
State: 0
Program Location:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner, Boolean& failoverDemandDone, String host, String failoverPartner, String protocol, SqlInternalConnectionTds connHandler, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.SqlServer.Management.Common.ConnectionManager.InternalConnect(WindowsIdentity impersonatedIdentity)
at Microsoft.SqlServer.Management.Common.ConnectionManager.Connect()
How can I create a copy of an existing sql server 2005 (live or offline) database and put it on the same server in a test database ?
View 4 Replies View RelatedOk, here is my dilemma
I have an application that has many sites. Each site has it's own database. The databases have common tables (ie the name and fields are the same) What I want to be able to do is when creating a new database, I want to be able to copy certain common table data from one database to another. I have run into an error because the table have an IDENTITY so this is not working
INSERT INTO Containers SELECT * FROM ADMS2_Indian_Point.dbo.Containers
I also tried
USE ADMS2_RSCS
GO
SET IDENTITY_INSERT Containers ON
GO
INSERT INTO Containers SELECT * FROM ADMS2_Indian_Point.dbo.Containers
GO
SET IDENTITY_INSERT Containers OFF
GO
I got an error saying that I need to have a column list. I am trying to use this for any tables, so my question is this..
Is there any way to get around using a column list or is there a way to dynamically create the column list?
Or, Is there a better way that I should be doing this?
Please keep in mind I am not a dba and everything I have learned about SQL is from my good pal Google :)
Thanks for any help
Is there a way to use SSIS to copy data from a SQLServer CE database to a SQL Server 2005 database?
I have a database that has only been used on a mobile device, but now I want to use it among several devices, so I want to copy the structure and data to a SQL Server 2005 database and expand it's scope.
Hi,
I am trying to copy one database from one ms sql server 2000 to another ms sql server 2005. I would like to copy all tables, stored procedures, any other objects and the data in this database to the new server. How can I do that? I can not use attach or restore database method because of permission problem. However, I can use Linked server. How can I use Linked server to do that? Any other clues?
Thanks.
Hi, I am trying to use SQL Server 2005 Enterprise Edition to copy tables from my local server to my hosts server at brinkster.com. In the old version you could use DTS, but everything seems to have got far more complicated now! Please can anybody guide me in the right direction? Thanks a lot.
How can I copy a table from my sql server 2000 db to my sql server 2005 express edition?
I have a project in VS.NET 2005 and I have a db in App_Data folder. However, when I look into that folder, there is nothing visible. I now need to copy a table from my existing sql server 2000 to my db located in my project's App_Data folder.
Any help would be appreciated..
Regards,