Adding a read only logins to a standby server

Hi,
I am running a standby server which backups my main Sql server. I also
use this server as readonly server and everything works fine with my
login but I no matter what I try I am not able to add new users to be
able to read from the standby server. Besides trying to add users
directly to the standby server (?which of course failed) I also tried
to add the same login on both servers allowing the new login to read
from the main database and then performed the "backup-restore"
routine.
Thanks,
RanIf you want this destination to become an available source
database, you must select the Allow database to assume
primary role check box. If this box is not selected, this
destination database will not be able to assume the source
database role in the future. If you have selected the
Allow database to assume primary role check box, you must
also specify the Transaction Log Backup Directory on the
destination database to which the logs will be backed up.
Koohyar
This posting is provided "AS IS" with no warranties, and
confers no rights.
>--Original Message--
>Hi,
>I am running a standby server which backups my main Sql
server. I also
>use this server as readonly server and everything works
fine with my
>login but I no matter what I try I am not able to add new
users to be
>able to read from the standby server. Besides trying to
add users
>directly to the standby server (.which of course failed)
I also tried
>to add the same login on both servers allowing the new
login to read
>from the main database and then performed the "backup-
restore"
>routine.
>Thanks,
>Ran
>.
>|||Here is a totally unsupported hack to the system tables. Use at your own
risk.
This runs on the standby server. It needs a linked server path to the
primary server. Create the logins on the Primary server and give them
permissions on the databases on the primary server. Run the following
script, changing the source server and the login name. Make sure the login
does not exist on the stand-by server before running this script.
insert master.dbo.sysxlogins
([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
[dbid], [language])
select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
[dbid], [language]
from [PrimaryServerName].master.dbo.sysxlogins
where [name] = 'MyLoginName'
and srvid is NULL
Again, this is totally unsupported and is completely at your own risk. If
you don't understand what it is doing, don't run it.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"ran" <rveierov@.hotmail.com> wrote in message
news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> Hi,
> I am running a standby server which backups my main Sql server. I also
> use this server as readonly server and everything works fine with my
> login but I no matter what I try I am not able to add new users to be
> able to read from the standby server. Besides trying to add users
> directly to the standby server (.which of course failed) I also tried
> to add the same login on both servers allowing the new login to read
> from the main database and then performed the "backup-restore"
> routine.
> Thanks,
> Ran|||Hi Koohyar,
I have no problem in making the destination become an available source
database and i don't see how this may help me solve my problem with
adding new logins to my secondary database.
thanks,
Ran
"koohyar[MSFT]" <koohyd@.online.microsoft.com> wrote in message news:<0a5701c38cea$0d3244e0$a001280a@.phx.gbl>...
> If you want this destination to become an available source
> database, you must select the Allow database to assume
> primary role check box. If this box is not selected, this
> destination database will not be able to assume the source
> database role in the future. If you have selected the
> Allow database to assume primary role check box, you must
> also specify the Transaction Log Backup Directory on the
> destination database to which the logs will be backed up.
> Koohyar
> This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >--Original Message--
> >Hi,
> >
> >I am running a standby server which backups my main Sql
> server. I also
> >use this server as readonly server and everything works
> fine with my
> >login but I no matter what I try I am not able to add new
> users to be
> >able to read from the standby server. Besides trying to
> add users
> >directly to the standby server (.which of course failed)
> I also tried
> >to add the same login on both servers allowing the new
> login to read
> >from the main database and then performed the "backup-
> restore"
> >routine.
> >
> >Thanks,
> >Ran
> >.
> >|||Hi Geoff,
I have tried this on my development environment, but I am keep getting
the following error message:
"Server: Msg 259, Level 16, State 2, Line 1
Ad hoc updates to system catalogs are not enabled. The system
administrator must reconfigure SQL Server to allow this."
Is there anyway to get pass this restriction?
Thanks,
Ran
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OsCa4wOjDHA.2704@.TK2MSFTNGP10.phx.gbl>...
> Here is a totally unsupported hack to the system tables. Use at your own
> risk.
> This runs on the standby server. It needs a linked server path to the
> primary server. Create the logins on the Primary server and give them
> permissions on the databases on the primary server. Run the following
> script, changing the source server and the login name. Make sure the login
> does not exist on the stand-by server before running this script.
> insert master.dbo.sysxlogins
> ([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> [dbid], [language])
> select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> [dbid], [language]
> from [PrimaryServerName].master.dbo.sysxlogins
> where [name] = 'MyLoginName'
> and srvid is NULL
> Again, this is totally unsupported and is completely at your own risk. If
> you don't understand what it is doing, don't run it.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
>
> "ran" <rveierov@.hotmail.com> wrote in message
> news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> > Hi,
> >
> > I am running a standby server which backups my main Sql server. I also
> > use this server as readonly server and everything works fine with my
> > login but I no matter what I try I am not able to add new users to be
> > able to read from the standby server. Besides trying to add users
> > directly to the standby server (.which of course failed) I also tried
> > to add the same login on both servers allowing the new login to read
> > from the main database and then performed the "backup-restore"
> > routine.
> >
> > Thanks,
> > Ran|||Hi Geoff,
Thanks for the tip. it worked perfectly. (my other problem was solved
with sp_configure 'allow updates',1)
Thank,
Ran
rveierov@.hotmail.com (ran) wrote in message news:<fb241b13.0310072345.76828f86@.posting.google.com>...
> Hi Geoff,
> I have tried this on my development environment, but I am keep getting
> the following error message:
> "Server: Msg 259, Level 16, State 2, Line 1
> Ad hoc updates to system catalogs are not enabled. The system
> administrator must reconfigure SQL Server to allow this."
> Is there anyway to get pass this restriction?
> Thanks,
> Ran
>
> "Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message news:<OsCa4wOjDHA.2704@.TK2MSFTNGP10.phx.gbl>...
> > Here is a totally unsupported hack to the system tables. Use at your own
> > risk.
> >
> > This runs on the standby server. It needs a linked server path to the
> > primary server. Create the logins on the Primary server and give them
> > permissions on the databases on the primary server. Run the following
> > script, changing the source server and the login name. Make sure the login
> > does not exist on the stand-by server before running this script.
> >
> > insert master.dbo.sysxlogins
> > ([srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> > [dbid], [language])
> > select [srvid], [sid], [xstatus], [xdate1], [xdate2], [name], [password],
> > [dbid], [language]
> > from [PrimaryServerName].master.dbo.sysxlogins
> > where [name] = 'MyLoginName'
> > and srvid is NULL
> >
> > Again, this is totally unsupported and is completely at your own risk. If
> > you don't understand what it is doing, don't run it.
> >
> > --
> > Geoff N. Hiten
> > Microsoft SQL Server MVP
> > Senior Database Administrator
> > Careerbuilder.com
> >
> >
> >
> > "ran" <rveierov@.hotmail.com> wrote in message
> > news:fb241b13.0310070716.7eb1ac5d@.posting.google.com...
> > > Hi,
> > >
> > > I am running a standby server which backups my main Sql server. I also
> > > use this server as readonly server and everything works fine with my
> > > login but I no matter what I try I am not able to add new users to be
> > > able to read from the standby server. Besides trying to add users
> > > directly to the standby server (.which of course failed) I also tried
> > > to add the same login on both servers allowing the new login to read
> > > from the main database and then performed the "backup-restore"
> > > routine.
> > >
> > > Thanks,
> > > Ransql

Adding a new user login

In Ms SQL 2000, I want to be able to share my database with another user but
I only want them to be able to look at the database and not be able to
change anything.

Does anyone know how I go about doing this?Just use sp_adduser from the desired db & use sp_addrolemember if necessary

Add MS SQL 7.0 server in MS SQL 2000 SEM

Hi.
Rookie question.
Is it possible to add a MS SQL 7.0 server in a MS SQL 2000 Server Enterprise
Manager consol? I've added a login on the MS SQL 7.0 server, dedictaed to
the MS SQL 2000 login, but the 7.0 server appear to be "not associated with
a trusted SQL Server connection" when I try to register the 7.0 server in
the 2000 consol.
Please guide me on this one if you can.
Regards
Mr. Smith
Hi
SQL Server 2000 EM can manage SQL Server 7.0 installations.
Your problem is security. Are you logged on using an account that has
permissions on the SQL Server 7.0 server and domain? If not, you need to be
given the rights. Else, connect using SQL Server security by passing in a
Username and Password.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:e08lOn32EHA.924@.TK2MSFTNGP14.phx.gbl...
> Hi.
> Rookie question.
> Is it possible to add a MS SQL 7.0 server in a MS SQL 2000 Server
Enterprise
> Manager consol? I've added a login on the MS SQL 7.0 server, dedictaed to
> the MS SQL 2000 login, but the 7.0 server appear to be "not associated
with
> a trusted SQL Server connection" when I try to register the 7.0 server in
> the 2000 consol.
> Please guide me on this one if you can.
> Regards
> Mr. Smith
>

Add MS SQL 7.0 server in MS SQL 2000 SEM

Hi.
Rookie question.
Is it possible to add a MS SQL 7.0 server in a MS SQL 2000 Server Enterprise
Manager consol? I've added a login on the MS SQL 7.0 server, dedictaed to
the MS SQL 2000 login, but the 7.0 server appear to be "not associated with
a trusted SQL Server connection" when I try to register the 7.0 server in
the 2000 consol.
Please guide me on this one if you can.
Regards
Mr. SmithHi
SQL Server 2000 EM can manage SQL Server 7.0 installations.
Your problem is security. Are you logged on using an account that has
permissions on the SQL Server 7.0 server and domain? If not, you need to be
given the rights. Else, connect using SQL Server security by passing in a
Username and Password.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Mr. Smith" <nospam@.blindfolded.gone> wrote in message
news:e08lOn32EHA.924@.TK2MSFTNGP14.phx.gbl...
> Hi.
> Rookie question.
> Is it possible to add a MS SQL 7.0 server in a MS SQL 2000 Server
Enterprise
> Manager consol? I've added a login on the MS SQL 7.0 server, dedictaed to
> the MS SQL 2000 login, but the 7.0 server appear to be "not associated
with
> a trusted SQL Server connection" when I try to register the 7.0 server in
> the 2000 consol.
> Please guide me on this one if you can.
> Regards
> Mr. Smith
>

Add member failed for DatabaseRole 'db_datareader'

I tried to give myself datareader and datawriter for my domain login and got this error below. I'm logged into the server with my domain account which has admin rights.

TITLE: Microsoft SQL Server Management Studio

Add member failed for DatabaseRole 'db_datareader'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Add+member+DatabaseRole&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Cannot use the special principal 'dbo'. (Microsoft SQL Server, Error: 15405)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15405&LinkId=20476

BUTTONS:

OK

Because you are conected as a sysadmin, you are also the dbo of the database. You cannot add dbo as a member of any fixed role. dbo already has the permissions of db_datareader and db_datawriter.

Thanks
Laurentiu

|||Thanks!

Add Login

I want to add login in SQL Server 2005 through Query Analyzer and grant him
System Administrative Role through query analyzer, can any one help me out?
hi,
that is quite easy, according to SQl Server 2005 this is:
CREATE LOGIN SomeUser WITH PASSWORD = 'SomePassword'
sp_addsrvrolemember 'SomeUser','sysadmin'
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de

add domain login from management studio

We have a SQL Server 2005 SP2 server that also functions as a
secondary domain controller in active directory. Everything works
extremely well except for 1 bug that was found today. Currently
windows authentication is the only method. When creating a new sql
login either physically at the server or through a remote desktop
connection the user is found in the domain and works just fine.
However when trying to do the same process using management studio
from a desktop connecting to the sql instance as soon as you try to
find the user in the domain you get this error: "The program cannot
open the required dialog box because it cannot determine whether the
computer name "COMPNAME" is joined to a domain."
I have sysadmin privileges on the sql server and if I use my login
name on the server physically I am able to create logins.
Any help would be greatly appreciated.
Thanks
MattI've previously posted a bug to connect.microsoft.com about this. Please go
there
(https://connect.microsoft.com/SQLSe...=126
183) and let them know of your problem.
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"Matt Ziegler" wrote:

> We have a SQL Server 2005 SP2 server that also functions as a
> secondary domain controller in active directory. Everything works
> extremely well except for 1 bug that was found today. Currently
> windows authentication is the only method. When creating a new sql
> login either physically at the server or through a remote desktop
> connection the user is found in the domain and works just fine.
> However when trying to do the same process using management studio
> from a desktop connecting to the sql instance as soon as you try to
> find the user in the domain you get this error: "The program cannot
> open the required dialog box because it cannot determine whether the
> computer name "COMPNAME" is joined to a domain."
> I have sysadmin privileges on the sql server and if I use my login
> name on the server physically I am able to create logins.
> Any help would be greatly appreciated.
> Thanks
> Matt
>|||The problem that you are having is that when the SQL GUI opens the dialog to
select a user, it defaults to the local machine. Because the machine is
running as a domain controller it has no local accounts, and no local accoun
t
database.
Please go to connect.microsoft.com
(https://connect.microsoft.com/SQLSe...=126
183) and post additional information reguarding the issue.
--
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"Matt Ziegler" wrote:

> We have a SQL Server 2005 SP2 server that also functions as a
> secondary domain controller in active directory. Everything works
> extremely well except for 1 bug that was found today. Currently
> windows authentication is the only method. When creating a new sql
> login either physically at the server or through a remote desktop
> connection the user is found in the domain and works just fine.
> However when trying to do the same process using management studio
> from a desktop connecting to the sql instance as soon as you try to
> find the user in the domain you get this error: "The program cannot
> open the required dialog box because it cannot determine whether the
> computer name "COMPNAME" is joined to a domain."
> I have sysadmin privileges on the sql server and if I use my login
> name on the server physically I am able to create logins.
> Any help would be greatly appreciated.
> Thanks
> Matt
>

Add Database Role to Login Name

I have a SQL Server 2000 database that I restore often.
After the database is restored I need to add database security role to Login
Name Chicago\CH-datareader-K.
How would I add the database role 'db_datareader' to Login Name
Chicago\CH-datareader-K that correponds to User Name CH-datareader-K.
Please help me create the correct syntax so I add this to my restore script.
Thanks,Syntax
sp_addrolemember [ @.rolename = ] 'role' ,
[ @.membername = ] 'security_account'
So it should be
sp_addrolemember 'db_datareader','CH-datareader-K'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Joe K." <Joe K.@.discussions.microsoft.com> schrieb im Newsbeitrag
news:F7FFAA86-280E-45B2-93E3-F52AAB86D203@.microsoft.com...
> I have a SQL Server 2000 database that I restore often.
> After the database is restored I need to add database security role to
> Login
> Name Chicago\CH-datareader-K.
> How would I add the database role 'db_datareader' to Login Name
> Chicago\CH-datareader-K that correponds to User Name CH-datareader-K.
> Please help me create the correct syntax so I add this to my restore
> script.
> Thanks,

add a login to a database role (was "Very Confusing")

This stuff makes no sense what so ever,

In the Books on Line of MSSQL2000
In "Adding a Member to a SQL Server Database Role"

There is this
"Note : When you add a Windows NT 4.0 or Windows 2000 login without a user account in the database to a SQL Server database role, SQL Server creates a user account in the database automatically, even if that Windows NT 4.0 or Windows 2000 login cannot otherwise access the database."

I mean how can anyone add a login to a database role without making the login a user of the database.:shocked:
Also if it worked, a new fancy feature to add new logins??:eek:
Plz help:SI believe it adds it as a user automatically.|||I believe it adds it as a user automatically.

But i cannot do it:eek:
"user" must exist before "role".
If you can do it,plz explain:)|||The following discussion assumes that BUILTIN\Administrators is part of the SQL Server sysadmin group:

1. Create a junk Windows login
2. Make that login a member of the machine's Administrators group
3. Start SQL Enterprise Mangler
4. Select the target database
5. Create a new (junk) role within the database.
6. Verify there is no user in the db for your junk login
7. Add your junk Windows login to your junk role.
8. Verify there is now a user in the db for your junk login

The reason that you could add the login to the role was because they implicitly have access to the database because they are members of the sysadmin role. Once you assign them to a role within the database, SQL Server needs some way to store that membership information, so it has to create a user where none explicitly existed before.

-PatP|||7. Add your junk Windows login to your junk role.
-PatP

1&2>created a user account with account type computer administrator.
3>started enterprise manager
4>selected a target db
5>created junk dbrole
6>verified no junk user
7>double clicked junk dbrole, clicked add..
:confused: junk user not there:confused:
Only users who are database "users" are appearing.

I am working on an XP,maybe thats why the difference.|||So you can see the junk login inside the junk group, but you can not see the junk login in the database users ?!?!

-PatP|||No, the junk login does not appear when i click the add button of the junk role.

The junklogin will appear only if i give junklogin access to the SQL server first and then make the junklogin a user of the database first.
Only after this is done, can i see the junklogin, infact any login works this way.|||I'm one of the "scripts our us" kind of guys... Rather than asking you to do 10,000 clicks, can you run:DECLARE @.cJunque sysname
SELECT @.cJunque = 'MyMachine\JunqueLogin' -- Machine Administrator

EXECUTE sp_helpuser @.cJunque -- Prove "no one home"

EXECUTE sp_addrole 'Junque' -- Create group
EXECUTE sp_helprole 'Junque' -- Confirm still empty

EXECUTE sp_addrolemember 'Junque', @.cJunque -- Add junk login

EXECUTE sp_helprole 'Junque' -- See who's "home" now
EXECUTE sp_helpuser @.cJunque -- Should be a user

EXECUTE sp_droprole 'Junque' -- Tidy up...
EXECUTE sp_dropuser @.cJunque -- ...completely...and post the output?

-PatP|||That worked :D
It even says in Enterprise Manager, "Database Access : Via group membership" for junkuser.
Only i think the user has to be droped before the group.

I dont think this can be done through Enterprise Manager directly??

:beer: Thanks.:beer:|||Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
New role added.

(1 row(s) affected)

'piii\dbtemp' added to role 'Junque'.

(1 row(s) affected)

Server: Msg 15198, Level 16, State 1, Procedure sp_helpuser, Line 189
The name supplied (piii\junkuser) is not a user, role, or aliased login.
Server: Msg 15144, Level 16, State 1, Procedure sp_droprole, Line 53
The role has members. It must be empty before it can be dropped.

User has been dropped from current database.

AD groups and database roles

We have AD and SQL Server 2005.
I have made grants to a database role called 'App Users'. I then set up
the AD group as a login, and tried to map to the proper database and the App
Users database role. This last step is failing with a 'principal cannot be
found' error. Is there any limitation to use AD groups in this way? Seems
that it should be pretty straightforward.
Thanks,
DavidDavid
I could not reproduce it , can you provide a script or step by step what
you did so far?
1) Crate a database Role called 'MyRole'
2) Create a new login called 'Test'
3) Add the the new user (mapped to the login) to the desired database
4) Add a database user to the Role
"David R" <DavidR@.discussions.microsoft.com> wrote in message
news:99E59589-24C0-44C1-9335-6B039E2F91FE@.microsoft.com...
> We have AD and SQL Server 2005.
> I have made grants to a database role called 'App Users'. I then set up
> the AD group as a login, and tried to map to the proper database and the
> App
> Users database role. This last step is failing with a 'principal cannot
> be
> found' error. Is there any limitation to use AD groups in this way?
> Seems
> that it should be pretty straightforward.
> Thanks,
> David|||Thanks for the response, but never mind. I tried it again, after having a
new group created, and it worked without a problem. Don't know why it faile
d
the first time.
David
"Uri Dimant" wrote:

> David
> I could not reproduce it , can you provide a script or step by step what
> you did so far?
>
> 1) Crate a database Role called 'MyRole'
> 2) Create a new login called 'Test'
> 3) Add the the new user (mapped to the login) to the desired database
> 4) Add a database user to the Role
>
>
> "David R" <DavidR@.discussions.microsoft.com> wrote in message
> news:99E59589-24C0-44C1-9335-6B039E2F91FE@.microsoft.com...
>
>

actual creator of a database

Does anyone know how to obain the actual creator of a database by using TSQL or SP? I need to know actual Login not DBO.

Thanks.

You can access the system table directly:

select suser_sname(sid)
from master..sysdatabases
where name = 'tempdb'

There maybe an inbuilt T-SQL function that would tell you this, which would be a preferable way of getting the info.

HTH!

|||

You cannot do this (exactly) that I know of. You can get the owner of the database using the code in the other post:

select suser_sname(sid)
from master..sysdatabases
where name = 'tempdb'

or

select name, suser_sname(owner_sid)

from sys.databases

The owner can be changed though using: sp_changedbowner, so you are actually getting the current owner. That may be good enough for your needs, but it is a caveat you should understand.

Activity Monitor - host entries blank for sql login ?

The activity monitor is not showing entries for hosts which connect via sql logins (odbc), is this normal? Where can I find a list of protocols in which the hostname/ip can be picked up. We're on SQL 2005 SP1/Build 2153

Thanks

The hostname should show up if the user is connecting from a Windows box using ODBC or OLE-DB. If you are using a JDBC driver or comming from a unix machine this field is typically blank. This can be adjusted by adding the "Workstation ID" paramater to the connection string and setting it to equal the client machines host name. This object probably doesn't exist on all drivers. The string name may also be slightly different so some trial and error may be required.|||

The connections in question are from Windows OS (2000/XP) machines, no JDBC or UNIX involved......