Showing posts with label login. Show all posts
Showing posts with label login. Show all posts

Tuesday, March 27, 2012

creating a windows athentication login

Hi all. Here is my problem. Computer A has sql server 2005 express installed on it together with sql management studio. Computer B has only sql management studio. Computers are members of a corporate domain and are running xp professional. After starting management studio on computer B the databese engin on computer A is visible in the 'select server to connect to' dialog under network servers. When i try to connect i get the message login failed - so i try to add a windows athentication login for the appropriate user on computer A. The problem is as follows: Under 'Create new login/Select user or group/location' no network location apear and i can not add a network user name. This seem to be a network connectivity issue. I have, however, adjusted network connectivity via surface area configuration and enabled tcp/ip and named pipes - the server is visible form another computer as i have prviously mentioned.

I am not sure how you go there in the GUI. Do you right-click the Security->Logins folder and choose New Login?

From T-SQL, you can just use

CREATE LOGIN domain\user FROM WINDOWS

creating a user with dbo access

Hi,

I need to create a user with dbo access to a specific database using sql. I created a login and added a user to the login. How do i grant dbo privilege to this user (using sql)? Is there any system stored procedure for this? Please help.

Thanks in advance

Hi. Try

sp_changeowner [@.loginame=]'login'

Changes the owner of the current database.

sql

Sunday, March 25, 2012

Creating a user for replication

Anyone have the steps (permissions and etc...) to create a user for use only in replications instead use "sa" login ?we have created a dedicated login for the Replication with sa rights|||but the user need to have sa role ?!|||Yes it needs to have the sa Rights..sql

Monday, March 19, 2012

Creating a Password Retrieval Stored Procedure

I would like to create a stored procedure to pass a login name and password
to and have it verify the info and return a few fields of data back to the
calling function from a javascript page. I have tried a few things but can'
t
seem to verify the data and return a value to verify the data.One method is to use NOT EXISTS:
CREATE PROC MyProc
@.UserID varchar(128),
@.Password varchar(128)
AS
SET NOCOUNT ON
IF NOT EXISTS
(
SELECT MyData
FROM Users
WHERE UserID = @.UserID AND
Password = @.Password
)
BEGIN
RAISERROR ('Invalid user or password', 1, 0)
END
SELECT MyData FROM MyTable
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"EvanK" <EvanK@.discussions.microsoft.com> wrote in message
news:210A9660-5874-4AA0-9DF9-1CFE9487868B@.microsoft.com...
>I would like to create a stored procedure to pass a login name and password
> to and have it verify the info and return a few fields of data back to the
> calling function from a javascript page. I have tried a few things but
> can't
> seem to verify the data and return a value to verify the data.|||I changed the code as follows:
CREATE PROCEDURE dbo.MM_Chk_User
@.UserID varchar(128),
@.Password varchar(128)
AS
SET NOCOUNT ON
IF NOT EXISTS
(
SELECT First_Name, Last_Name
FROM LogonNames
WHERE Name = @.UserID AND
Password = @.Password
)
BEGIN
RAISERROR ('Invalid user or password', 1, 0)
END
SELECT First_Name, Last_Name
FROM LogonNames
WHERE Name = @.UserID AND
Password = @.Password
GO
I get the following error
[SQL Server]Invalid value 0 for state. Valid ranges is from 1 to 127
"Dan Guzman" wrote:

> One method is to use NOT EXISTS:
> CREATE PROC MyProc
> @.UserID varchar(128),
> @.Password varchar(128)
> AS
> SET NOCOUNT ON
> IF NOT EXISTS
> (
> SELECT MyData
> FROM Users
> WHERE UserID = @.UserID AND
> Password = @.Password
> )
> BEGIN
> RAISERROR ('Invalid user or password', 1, 0)
> END
> SELECT MyData FROM MyTable
> GO
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "EvanK" <EvanK@.discussions.microsoft.com> wrote in message
> news:210A9660-5874-4AA0-9DF9-1CFE9487868B@.microsoft.com...
>
>

Sunday, March 11, 2012

Creating a new login with limited rights

what combonation do you use to create a user with rights to add alertsand to
create backup jobs. This user should be able to modify databases or other u
sers.Hi,
create backup jobs - Any one with public role can create the job
Create Alerts - Only members of sysadmin fixed server role can create the
alerts
Modifying users - Provide security admin server fixed role
Modify databases - Disk admin server fixed role
Note:
You have to do all the admin functions using this user, Preferably you can
assign 'Sysadmn' fixed server role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:232E3D1A-E424-4DC7-AD3B-6C97DF2947A9@.microsoft.com...
> what combonation do you use to create a user with rights to add alertsand
to create backup jobs. This user should be able to modify databases or other
users.|||-- Hari wrote: --
Hi,
create backup jobs - Any one with public role can create the job
Create Alerts - Only members of sysadmin fixed server role can create the
alerts
Modifying users - Provide security admin server fixed role
Modify databases - Disk admin server fixed role
Note:
You have to do all the admin functions using this user, Preferably you can
assign 'Sysadmn' fixed server role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:232E3D1A-E424-4DC7-AD3B-6C97DF2947A9@.microsoft.com...
> what combonation do you use to create a user with rights to add alertsand
to create backup jobs. This user should be able to modify databases or other
users.
Bummer, Since I'm new at this my role as DBA needs to be limited until I lea
rn more. What is the posibility of creating a user on our live server that c
an creat backup jobs, assign notification in the jobs created, and to view t
he event long on the s
erver itself, not the SQL log. Also, I want to limit the ability to change a
ny settings as far as the databases are concerned.
My login I created contains the following.
No server roles are selected
All databases are selected
Public-is checked
db_securityadmin is checked
db_backupoperator is checked

creating a new login and user

hi,
I have a database : 'harshal'
and want to create a login/user called : 'thisuser'
and want to make him the db_owner for the database.
I am using the following script .

if exists (select * from master.dbo.syslogins where loginname=N'thisuser')
exec sp_droplogin 'thisuser'
if not exists (select * from master.dbo.syslogins where loginname = N'thisuser')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132)
select @.logindb = N'harshal', @.loginlang = N'us_english'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'thisuser', null, @.logindb, @.loginlang
if not exists (select * from dbo.sysusers where name = N'thisuser' and uid < 16382)
EXEC sp_grantdbaccess N'thisuser', N'harshal'
exec sp_defaultdb 'thisuser','harshal'
exec sp_addrolemember 'thisuser','db_owner'
exec SP_ADDUSER 'thisuser','harshal'
end

but it gives me the following error:

New login created.
Granted database access to 'thisuser'.
Default database changed.
Server: Msg 15014, Level 16, State 1, Procedure sp_addrolemember, Line 37
The role 'thisuser' does not exist in the current database.
Server: Msg 15023, Level 16, State 1, Procedure sp_grantdbaccess, Line 126
User or role 'harshal' already exists in the current database.

any help would be greatly appreciated.
regards,
harhsal.exec [sp_addrolemember] 'db_owner','thisuser':)|||how could I miss that one :(
thanks anyways.
regards,
Harshal.|||looks like u r new to these sp ... can check in online book.

correct code is as following ...

-------
if exists (select * from master.dbo.syslogins where loginname=N'thisuser')
exec sp_droplogin 'thisuser'
if not exists (select * from master.dbo.syslogins where loginname = N'thisuser')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132)
select @.logindb = N'harshal', @.loginlang = N'us_english'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'thisuser', null, @.logindb, @.loginlang
if not exists (select * from dbo.sysusers where name = N'thisuser' and uid < 16382)
EXEC sp_grantdbaccess 'thisuser', 'harshal'
exec sp_addrolemember 'db_owner','thisuser'
end

-------

Thursday, March 8, 2012

creating a login with only backup and alert rights

What is the combination of rights to give a user access to only backup and a
lerts.Hi,
Backup the database, You can assign the database wise role
"db_backupoperator"
How to assign the backup operator role to test user
1. Use database name
2. Execute sp_addrolemember db_backupoperator ,test
To create Alerts , the user should be the member of SYSADMIN fixed server
role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:5190A3C1-E640-4F1C-BB84-8C242569ABFA@.microsoft.com...
> What is the combination of rights to give a user access to only backup and
alerts.

Creating a login after the backup was restored with existent users

Is there a way to create the login id after the database backup with users
was restored into this new SQL Server. The problem is that I am trying to
create the login id for the user that owns practically all the database
objects.
So I can't drop that user to execute sp_grantdbaccess after creating the
login id.
thanks
Cathy B
If you already created the login on the server and this is a SQL Server
user, then you need to go into each of the database and run:
EXEC sp_change_user_login 'REPORT.
Any users that show up in this list need to be have the following command
executed for them:
EXEC sp_change_users_login 'AUTO_FIX','user'
If you haven't added the logins to the new server yet (which is
preferrable), you run sp_help_revlogin on the old server. You then run the
resulting scrip on the new server to create the logins. You run this AFTER
the restores.
"Cathy Boehm" <CathyBoehm@.discussions.microsoft.com> wrote in message
news:EF2EFDC8-328C-410A-908E-FAA0E49B9220@.microsoft.com...
> Is there a way to create the login id after the database backup with users
> was restored into this new SQL Server. The problem is that I am trying to
> create the login id for the user that owns practically all the database
> objects.
> So I can't drop that user to execute sp_grantdbaccess after creating the
> login id.
> thanks
> --
> Cathy B

Creating a login after the backup was restored with existent users

Is there a way to create the login id after the database backup with users
was restored into this new SQL Server. The problem is that I am trying to
create the login id for the user that owns practically all the database
objects.
So I can't drop that user to execute sp_grantdbaccess after creating the
login id.
thanks
--
Cathy BIf you already created the login on the server and this is a SQL Server
user, then you need to go into each of the database and run:
EXEC sp_change_user_login 'REPORT.
Any users that show up in this list need to be have the following command
executed for them:
EXEC sp_change_users_login 'AUTO_FIX','user'
If you haven't added the logins to the new server yet (which is
preferrable), you run sp_help_revlogin on the old server. You then run the
resulting scrip on the new server to create the logins. You run this AFTER
the restores.
"Cathy Boehm" <CathyBoehm@.discussions.microsoft.com> wrote in message
news:EF2EFDC8-328C-410A-908E-FAA0E49B9220@.microsoft.com...
> Is there a way to create the login id after the database backup with users
> was restored into this new SQL Server. The problem is that I am trying to
> create the login id for the user that owns practically all the database
> objects.
> So I can't drop that user to execute sp_grantdbaccess after creating the
> login id.
> thanks
> --
> Cathy B

Creating a Login

Hi;

This may not be the correct location for this thread; but it does relate to what I'm trying to do.

I have SQL Server [2005] setup on a Windows XP pro system. I also have Windows 2003 server setup (on another system). I want to setup a login account for SQL Server using a domain account (defined on the Windows 2003 server configured with Active Directory). When I open locations list in the new Login dialog box I only see the local system [computer name]; no domain is listed [in the list].

Now I can see the other computers on the network (all registered with the domain managed by active directory); and can access the other computers (via domain account signon credentials), My guess is that things are setup properly.

What I'm not getting is how do I use domain accounts when setting up a new login for SQL Server. I have read a bunch of things and tried them all (like making sure DNS is installed on server system; loginig on to SQL Server machine with domain account) but nothing seems to work.

So my question is; does anyone know what I'm missing here -- should I be able to see the domain listed in the location list for SQL Server (that is installed on a XP system)?

Peter

hi peter,

Your windows XP should be a member of the domain.

In short, you should be able to login to the domain from your XP box.

for instruction on joining a domain please consult this links.

http://www.windowsnetworking.com/articles_tutorials/wxpjoind.html

thanks,

joey

|||

Hi;

I guess my question was unclear. All computers in the network are part of the domain. I'm not looking at how to connect an XP box to the domain; as my question stated; I am looking for information on creating a login account in SQL SERVER 2005 using a domain account. The problem I'm having is I cannot see any domain listed in the location list when creating thew new login for SQL SERVER.

Peter

|||

is this what u r looking for?

CREATE LOGIN [YourDomainName\DomainUserName] FROM WINDOWS;

Madhu

|||

Hi;

Wasn't looking for script. I am using SQL Server Management Studio and I am creating a new login from the new login dialog. One of the options in the new login dialog is a search (for user) accessed via the search button on the main dialog. This will bring up the standard Select User or Group dialog where you can select from a list of users recongized by the system. On the select User or Group dialog there is a button marked Location; and when selected it will bring up the standard location dialog. This dialog should show all the possible locations that can be used for locating a user. On my system it only shows the local computer [name]. The local computer is registered and belongs to a domain. My assumtion was that I should be able to see the domain [name] as one of the locations listed. It is not. I am trying to figure out how I get the domain to show up in the location list. The local computer is running Windows XP pro; and I wan't to know how to have it configured so that it shows the domain in the location list. I have tried a bunch of things recommended by others but I still cannot see the domain in the location list.

If I attempt to do what you had suggested via the dialog gui (i.e. DOMAINNAME\USERNAME) I get the following error when I do a check names on the specified user:

The object named "adomain\adomainuser" is not from the a domain listed in the Select Location dialog box, and is therefore not valid."

Peter|||

thats wonderful.

then....

Why dont you just

logging into to your XP box as the Domain Administrator?

if my memory servers me right

thats a member of the administrators NT group

which is by default administrator to Sql server as well

via builtin\administrator.

From there you should be able to access all Domain resources

If that is not possible... hehehe

try logging in using any of your domain accounts.

before doing anything in the box.

by the way...

Are your sql services running under

the domain account or local XP account?

The steps are of course.

1. make sure your box is a member of the domian

2. make sure you can login to the box using a domain account

3. your sql box services is runing under a domain account

no 3 should be a sure way to answer your problem

|||

Also I would like to suggest to contact your network administrator to see controllers in your domain. Depending on how you’ve set up Active Directory, you could miss a few of your domain controllers. The second way is a little bit more complicated, but barring any unforeseen circumstances, should always return a list of all your domain controllers.

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?
In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change. A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:

> Is there a way to create a login and password, or change a login's password
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?
|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
[vbcol=seagreen]
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change. A
> dialog box will open and you can change the password in there, near the top.
> Type in the password in the Password box, and the same password again in the
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have a
password.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
[vbcol=seagreen]
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change. A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
> Is there a way to create a login and password, or change a login's password
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change. A
> dialog box will open and you can change the password in there, near the top.
> Type in the password in the Password box, and the same password again in the
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
> > Is there a way to create a login and password, or change a login's password
> > in management studio (sql 2005) without using a sql script? Any wizard or
> > dialog box?|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have a
password.
--
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:
> > In the Management Studio, open the Object Explorer and expand your Server,
> > then Security, then Logins and right-click on the login you want to change. A
> > dialog box will open and you can change the password in there, near the top.
> > Type in the password in the Password box, and the same password again in the
> > Confirm Password box.
> >
> >
> > --
> > AndyP,
> > Sr. Database Administrator,
> > MCDBA 2003
> >
> >
> > "mkiger" wrote:
> >
> > > Is there a way to create a login and password, or change a login's password
> > > in management studio (sql 2005) without using a sql script? Any wizard or
> > > dialog box?

creating a login

Is there a way to create a login and password, or change a login's password
in management studio (sql 2005) without using a sql script? Any wizard or
dialog box?In the Management Studio, open the Object Explorer and expand your Server,
then Security, then Logins and right-click on the login you want to change.
A
dialog box will open and you can change the password in there, near the top.
Type in the password in the Password box, and the same password again in the
Confirm Password box.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:

> Is there a way to create a login and password, or change a login's passwor
d
> in management studio (sql 2005) without using a sql script? Any wizard or
> dialog box?|||Thanks, that works for server logins, but what about database users?
"AndyP" wrote:
[vbcol=seagreen]
> In the Management Studio, open the Object Explorer and expand your Server,
> then Security, then Logins and right-click on the login you want to change
. A
> dialog box will open and you can change the password in there, near the to
p.
> Type in the password in the Password box, and the same password again in t
he
> Confirm Password box.
>
> --
> AndyP,
> Sr. Database Administrator,
> MCDBA 2003
>
> "mkiger" wrote:
>|||You can go to the database, then Security, then Users, and add/remove the
users to/from the database and affect their overall database access that way
.
You can then grant or revoke permissions at that level. The database user
does not have a password property, but is linked to a login which does have
a
password.
AndyP,
Sr. Database Administrator,
MCDBA 2003
"mkiger" wrote:
[vbcol=seagreen]
> Thanks, that works for server logins, but what about database users?
> "AndyP" wrote:
>

Sunday, February 19, 2012

creating / changing owner problem

I'm trying to create a new database and new login to allow a client
(through my asp.net page) to create their own database, username and
password. I've tried using the stored procedures I've found in BOL,
but I can't get it to work right. It keeps saying that I can't assign
this username as the db owner since it's already a user for the
database. I then read other posts about reassigning the db owner to
another dummy account and then trying to reassign it to the new one,
but that isn't working either. Can anyone look at my code and tell me
what I'm doing wrong? Thanks.

//create db login
qry = "sp_addlogin '" + username + "', '" + password + "', '" + dbname
+ "'";
cmd.CommandText = qry;
cmd.ExecuteNonQuery();

//grant login access to new database to new owner
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_grantdbaccess '" + username + "'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;

//connect to new database under sa account and change owner to new
account
tmpCon = new SqlConnection("Data Source=ourserver;User
ID=uid;Password=pwd;Initial Catalog=" + dbname + ";Network
Library=DBMSSOCN;");
tmpcmd = new SqlCommand("sp_changedbowner
'HolderUserDoNotDelete'",tmpCon);
tmpCon.Open();
tmpcmd.ExecuteNonQuery();
tmpcmd.CommandText = "sp_changedbowner '" + username + "'";
tmpcmd.ExecuteNonQuery();
tmpcmd.Dispose(); tmpcmd = null;
tmpCon.Close(); tmpCon.Dispose(); tmpCon = null;geoff (cakewalkr7@.hotmail.com) writes:
> I'm trying to create a new database and new login to allow a client
> (through my asp.net page) to create their own database, username and
> password. I've tried using the stored procedures I've found in BOL,
> but I can't get it to work right. It keeps saying that I can't assign
> this username as the db owner since it's already a user for the
> database. I then read other posts about reassigning the db owner to
> another dummy account and then trying to reassign it to the new one,
> but that isn't working either. Can anyone look at my code and tell me
> what I'm doing wrong? Thanks.

If you intend to make the login owner of the database, there is no
reason to create a user for him in the database, but just skip that
step. Once the login owns the database, he also has a user in it.

Note: A login in SQL Server is server-wide. A user is something in a
database, which can be connected to a login. The username and loginname
may be the same, but they don't have to.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp