Sp_Login: Transferring Users/SIDs Between Servers

Jan 2, 2001

My question is, why did I have to use the allegedly optional named argument @DefLanguage when using sp_AddLogin? I had often tried to use the @SID parameter of sp_AddLogin to synchronize users betwixt our servers. Previous efforts had failed.

Per Transact-SQL Help, here’s the syntax for AddLogin:

sp_addlogin [@loginame =] 'login'
[,[@passwd =] 'password']
[,[@defdb =] 'database']
[,[@deflanguage =] 'language']
[,[@sid =] 'sid']
[,[@encryptopt =] 'encryption_option']

We have over a dozen User DBs, and I’d written a cursor to add the login and then GRANT access to the appropriate DBs. I used the following variables:

DECLARE @Loginame VarChar(25) , @Passwd VarChar(25) , @DefDB VarChar(50)
DECLARE @SID VarBinary(16)

Then I SET the variables:
SET @Loginame = 'MyUserName'
SET @Passwd = 'UsersPwd'
SET @DefDB = 'UsersDB'
SET @SID = 0x6370170BDAAFF640AD5CEB586EA87C2C
-- UserSID for MyUserName from sister server

Then I EXECuted the proc.
EXEC sp_AddLogin @Loginame , @Passwd , @DefDB , @SID

This generated the following error (instead of ‘?’, I saw squares, as in unprintable characters):

Server: Msg 15033, Level 16, State 1, Line 0
'????????' is not a valid official language name.

I didn’t understand this at all at first, but out of desparation, I DECLAREd & SET the @DefLanguage argument...And it worked! That is:

DECLARE @Loginame VarChar(25) , @Passwd VarChar(25) , @DefDB VarChar(50)
DECLARE @SID VarBinary(16)
DECLARE @DefLanguage SysName

Then added:
SET @DefLanguage = NULL
-- per sp_Configure, RunValue = 0
-- Master..SysLanguages reveals that language ID = 0 corresponds to ‘us_english’

Then I EXECuted:
EXEC sp_AddLogin @Loginame , @Passwd , @DefDB , @DefLanguage , @SID

Success!

Again, my question is, why did I have to use the allegedly optional named argument @DefLanguage?

View 1 Replies


ADVERTISEMENT

Transferring Databases Between Servers

Feb 15, 2002

I need some advice on copying databases, stored procedures, views, logins ..etc from a SQL Serevr 7.0 server, to a new SQL Server 2000 server.
Is it better to backup the databases on one server and then restore them to the other. Or id it easier to use the Wizards to Import everything from new server, or to Export everything from the old server ?

Any advice would be appreciated. I need to ensure that I do not miss anything, in particular Stored Procedures in the old master database.

View 2 Replies View Related

Transferring Databases Between Servers

Jan 11, 2000

I tried using Import/Export to transfer a database from one SQL Server box to another using NT authentication.

The database appeared to transfer OK, however the process crashed with an error suggesting that a SQL Server login was not present.

How are the SQL Server logins transferred ?

Richard

View 2 Replies View Related

Transferring Backups Between Servers

Aug 27, 1999

Hi!
I have weekly full db backups, daily differential log backups and transaction log backups every 15 minutes on the prod. db server. What is the fastest way to make the staging server have the current version of the db. in the production server? Do I always have to take a full db. backup on the prod. server, zip the file, copy the file to staging, unzip the file and then restore it on staging? Is is possible to use the existing combination of full, diff and trans. log backups to make the restoration process faster?

Thank you

Praveena

View 4 Replies View Related

Transferring Objects Between Sql Servers

Feb 2, 2006

Transferring objects between sql servers having db created using different usernames :

We have a local sql2000 db created using username abc, and another SQL2000db at a remote location where we have hosted our database. In our remotelocation the db username is ourdomain. How do we transfer
all objects from local db created using username abc to remote db created using ourdomain.? Is it possible to exclude usernames while transferring objects between sql servers?? Please help

K006b

View 2 Replies View Related

Transferring Data Between Servers

May 14, 2006

I need to append data from a database on one server to a table in a databaseon a different server. Both servers are running SQL 7. How can that be done?Thanks.

View 2 Replies View Related

Transferring MS-Sql Database Between Two Servers

Dec 1, 2006

hello friends,


I need to transfer my database from one server to database on another server every 24 hours. I can create windows application but it will be cumbersome to write bulk of code . So can u suggest me some service or any other way through query or stored procedure by job scheduling which can run every 24 hours and move my data from one database on one server to another sql database. Both databases are sql server 2000 but servers are diffeerent so how to connect them while transferring dbs. ? plz help me , its urgent.

regards,
max

View 3 Replies View Related

Transferring Two Tables Between Servers (was Logic)

May 12, 2005

I have 2 tables with the following structure:
CREATE TABLE [dbo].[table1] (
[RID] [int] IDENTITY (1, 1) NOT NULL ,
[RText] [varchar] (400) NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]

CREATE TABLE [dbo].[Table2] (
[GrpRID] [int] IDENTITY (1, 1) NOT NULL ,
[GrpID] [varchar] (10) NOT NULL ,
[RID] [int] NOT NULL ,
[Status] [bit] NULL ,
[SortOrder] [int] NULL ,
[DateModified] [datetime] NULL
) ON [PRIMARY]
GO
I am transfering 2 table between 2 SQL server based on GrpRID from table2.
Since RID is identity in table1 sometimes it is different text for spesific
Rid in second server. Some how I need to get the match the right text from server1
to server2 and if text doesn't exists create a bew entry in table1 with the update to table2
wich should reflect correct RID.

View 1 Replies View Related

Ways Of Transferring Database Between Two Different Servers

Dec 1, 2006

hello friends,


I need to transfer my database from one server to database on another server every 24 hours. I can create windows application but it will be cumbersome to write bulk of code . So can u suggest me some service or any other way through query or stored procedure or by job scheduling which can run every 24 hours and move my data from one database on one server to another sql database. Both database systems are sql server 2000 but servers are diffeerent so how to connect them while transferring dbs. ? Any help is appreciated.

regards,
max

View 1 Replies View Related

DTS - Primary Keys Drop When Transferring Tables Between Servers

Sep 7, 2000

Hello All,

Has any1 noticed that when they are transferring SQL tables from one server (or machine) to another that the primary keys drop from the table (or is it just me). If so, has someone figured out why? and how to rectify this (apparent) error.

Many thanks in advance for any and all help,

Gurmi

View 1 Replies View Related

Auditing Users On Various SQL Servers

Jul 19, 2007

We have about 50 SQL Servers (most 2000 but some 2005) in different domains and behind firewalls and we implementing the PCI rules for the use of credit card data. One thing we need to do is to audit users and their rights. I have a stored proc that can run each night and record users and permissions.



My main problem is trying to bring all that data together on a central box and then parse through it to see if I have any offenders. The parsing is not the issue, but the polling is. I could use the SA account or create a audit account with SA rights but both of those solutions go against the PCI mantra.



Can someone who has done this give me some guidance as to what you did. I know that there are some third party tools that would probably do this, but my department is on a shallow budget and my overtime is free.



Any suggestions would be greatly appreciated. Thank you!



Jim Youmans

St. Louis

View 4 Replies View Related

Moving Users And Permissions Between Servers--urgent

Nov 15, 2000

Is there any easy way to move users between SQL Servers for a particular database?
We have tests and development servers I would like to make sure the user lists are in sync for that db.

Thanks

View 1 Replies View Related

SQL 2012 :: Automate DB Users To Get Data For Every Month On All Servers

Aug 13, 2015

I need to automate the db users to get the data for every month on all servers ....

View 9 Replies View Related

How Do I Restrict Access To The Report Servers Header From Users

Jun 16, 2007

Hi

I'm looking to deploy some SQL Server reports and I want to restrict the access that the users have. Currently when connecting to the reports site they have access to a lot of functionality through the header bar, for example
- Properties
- New Folder
- New Data Source
- My Subscriptions
- Site Settings
- Search
etc.

How can I disbale or hide all these options so that all the user sees is the list of reports?

Thanks in advance

Mark

View 1 Replies View Related

How To Synchronize SQL Server Login SIDs

Jan 11, 2007

Hi everyone...

On two instances of SQL Server 2005 I am using the same SQL login names, but the SID is different for each one. I would like to synchronize these IDs so that when I restore the production database to the developement database I don't have to remove and re-add the logins.

Is there a quick and easy way to do this?

Thanks,

Forch

View 3 Replies View Related

SQL Server Admin 2014 :: Service SIDs With Data Files On CIFS Share?

Jan 13, 2014

I want to use service SIDs for my SQL Service accounts but also want to have the data files on a NetApp filer CIFS share. The 2014 installer prevents installation if CIFS and Service SIDs are used. I tried to install with domain account on CIFS, and then to swap back to Service SIDs afterwards, but couldn't find a way to do it.

I granted the AD Computer account Full Control to the CIFS share, so it should work, but I just can't work it out at the moment.

View 0 Replies View Related

Reporting Services :: Adding (new) Child Domain Users To SSRS As System Users?

Jul 28, 2015

We have an existing SSRS server, and have just created a new child domain. We'll be migrating users from the parent to the child, and want to add the users of that new domain with access to SSRS. In the parent domain they are able to access, but after migration with the child domain account, they cannot.

I have added the group CHILDDomain Users with a system user role on SSRS, and PARENTDomain Users was already there.

Is there any additional step I should/could take to get this active?

View 5 Replies View Related

Why Out Of The Blue Would VPN Users Be Unable To Connect To Database And Local Users Are Unaffected?

Mar 6, 2008

I have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!

View 6 Replies View Related

SQL Security :: Did Not Create Any New Users And There Are No Other Users Listed In Accounts Section

Sep 28, 2015

I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.

View 2 Replies View Related

Stored Procedure To Get All Users Or All Users Of A Specific Country

Apr 30, 2008

hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){    WHERE fk_country = @fk_country}
who has an idea how to solve this problem?

View 9 Replies View Related

Sql Report Works Fine On Internal Servers - Hosed On External Servers - Need Some Help

Nov 21, 2007

I have a report that was designed using SQL Reporting Services that sits on a SQL reporting server. It's nothing too exciting, it is essentially a three page application with legal jumbo on pages 2 and 3 and applicant data in fields on page 1.

We use rectangles to force page breaks to page 2 and to page 3.

When running the report on the report server, it shows and prints fine.

When running the report from the QA website internally, it shows and prints just fine.

When running the report from the production website from a machine internally, it shows and prints just fine.

When running the report from outside of the company network, the report is jacked. It obliterates large chunks of text, crams text together, and creates blank pages.

I need help in determining where I even begin with trouble shooting this!

View 1 Replies View Related

Difference Between Database -- &&> Users And Security --&&> Users

Nov 28, 2006

Hi Team,

In SQL Enterprise Manager, when we expand "Database -->Users", we see the

users there. When we expand "Security --> logins" we see the same users there.

Can you differentiate these two.

Thanks

Santhosh

View 1 Replies View Related

Development Servers, Auto-update Live Servers

Aug 21, 2001

can anyone tell me if they know of a way to automate the update process from development servers to live server, with little interference from an administrator

I have a development team that are constantly updating their databases along with their ASP code, and want to publish changes an a weekly basis. They have asked me for a way to take their new structures, tables, procedures etc, and copy them to the live servers, but NOT to interfere with existing customer data.

Funny I know – and I hate the idea btw :(

Any references, contacts, 3rd party tool recommendations welcome,

Thanx,

Darren

View 1 Replies View Related

Linking SQL 2005 Servers To SQL 2000 Servers Problems

Sep 27, 2007

I am in the middle of a major migraton project, moving from x86 SQL 2000 to IA64 SQL 2005. I have a business need to link to several legacy servers. I have a number of problems I am trying to solve.

1) Linking a Kerberos server to a non-Kerberos server.
2) Linking x64 or IA64 servers to x86 servers.
3) Linking SQL 2005 to SQL 2000.

Two of the errors I am encountering are:
------------------------------
TCP Provider: An existing connection was forcibly closed by the remote host.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
OLE DB provider "SQLNCLI" for linked server "SCDC250DB" returned message "Communication link failure".
(Microsoft SQL Server, Error: 10054)
------------------------------
And
------------------------------
The OLE DB provider "SQLNCLI" for the linked server "SCDC250DB" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "SCDC250DB".
OLE DB provider "SQLCLI" for linked server "SCDC250DB" returned message "Invalid authorization specification".
(Microsoft SQL Server, Error: 7399)

If someone has worked through these problems before, I would appreciate it if you could direct me to the relevant documentation to resolve these issues.

Thanks!


Brandon Forest

Database Administrator

Data & Web Services Team

Sutter Connect Information Technologyforesb@sutterhealth.org

View 2 Replies View Related

List Users Then Disconnect Users

Jun 4, 2008

I want to be able to list all users connected/logged in a specific database and disconnect them all or a certain user.

can this be done in SQL and if so how?

View 1 Replies View Related

Users Get Blocked By Other Users Alot

Sep 26, 2007

We are using Navision with SQL server 2003.

What kind of methods is there out there to reduce hwo often this happens?

View 10 Replies View Related

Transferring Databases

Mar 21, 2007

Hi
I created some databases on my pc at work (using Visual Web Developer 2005 and Microsoft SQL Server Express), I now wish to move them onto my home machine, Can I do this by simply copying the .MDF files and then moving them into the App_Data folder of my new app?
Do MDF files contain everything (schema, data, stored procedures etc) or am I missing something?
Cheers, Adam

View 5 Replies View Related

Transferring Database

Mar 23, 2001

Hi !

Currently I need to transfer existing database to another server. Unfortunately, after restoring all database file, user information is not available. It is stored in Master database.

I would like to know how can I transfer user login, database access, store procedures to another server ?

Please advise. Thanks !

View 3 Replies View Related

Transferring Data From One Db To Another

Nov 22, 2000

In Sql 6.5 there was an option to Transfer the db. I used this quite often to make a duplicate test db of my production db. Under 7.0 I haven't found it so easy to do this. DTS wizard hasn't worked well for me. I can get it to transfer tables but not all of the objects. Any ideas? Thanks.

View 5 Replies View Related

Transferring Logins

Jan 7, 2001

I have to transfer a database to a new machine. I am planning on doing a backup and restore. My question is, will the logins and roles be transferred along with the database?

thanks

View 6 Replies View Related

Transferring Databases From 6.5 To 7

Dec 6, 1999

This is the situation: I had a SQL 6.5 server with some databases. I still have access to the .dat files, however, SQL 6.5 will not start anymore. Is it possible to copy the .dat files to the new sql 7 server and somehow get them imported into the new SQL 7 server? Any help would be greatly appreciated.

Thanks
Charl
(please also copy my email address if possible when you reply)

View 1 Replies View Related

Transferring DTS Packages...

Oct 19, 2005

Hi Everyone,
Have anyway come across any easy method of transfers DTS packages across two servers which are existing on two different networks.

I haven't been able to use the method mentioned in article "DTS How to...Transfer Packages between Servers.." since the two servers I'm trying to move TO and move FROM are on two different networks which aren't visible to each other for my to register from the same box.

Appreciate if someone to point me to a means of doing this or any articles that explain how to do this..

thank you

View 9 Replies View Related

Transferring Data

Apr 6, 2001

Hi,

We have a SQL 6.5 database that was expanded larger than was really necessary. I have created a smaller size database and am wondering what the easiest way would be transfer ALL of the data and objects from the existing database into the new one.
Is SQL transfer manager reliable? Are there any better ways?

Thanks,

Sundar.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved