Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Logging into SQL Server Instance

Author  Topic 

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-06 : 11:39:54
Is there any way to log into SQL Server Management studio when
1. I did not opt for windows authentication and
2. I forget my sql authentication account details

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-06 : 15:58:15
Windows authentication is always in place. You can't opt out of it.

But local admins are no longer sysadmins like they were in older versions of SQL Server. If you didn't add a Windows login or group to be sysadmin, you are pretty much out of luck. Uninstall/reinstall + restore.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-11-07 : 01:48:21
quote:
Originally posted by tkizer

Windows authentication is always in place. You can't opt out of it.

But local admins are no longer sysadmins like they were in older versions of SQL Server. If you didn't add a Windows login or group to be sysadmin, you are pretty much out of luck. Uninstall/reinstall + restore.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Thanks for the reply sir. Got my issue confirmed.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-12-09 : 05:21:56
I am trying to map more than one same database user to a single login. But i am unable to succeed.
But i am able to map one user of a database and one user of another database to a single login.

I request somebody to tell me whether there is way to map more than one user of a single database to a single login. And how to use credentials..

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-09 : 12:45:29
I don't understand what you are saying.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-12-10 : 08:07:25
quote:
Originally posted by tkizer

I don't understand what you are saying.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Let suppose we have created 3 users for db1 database and 3 users for db2 database.
I have created a login for example "cli1".
SQL Server is not allowing to map 2 users of db1 database to this login.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-10 : 13:32:31
You can't map multiple users from one database to a single login. It's a 1:1 relationship.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-12-10 : 15:38:47
You can map a single user to multiple databases. Schema may cause problem if not handled.
You can also use Windows groups.

djj
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-10 : 15:45:16
quote:
Originally posted by djj55

You can map a single user to multiple databases. Schema may cause problem if not handled.
You can also use Windows groups.

djj



sgondesi wants the opposite --> mapping multiple users in one database to one login.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-12-11 : 09:01:40
quote:
Originally posted by djj55

You can map a single user to multiple databases. Schema may cause problem if not handled.
You can also use Windows groups.

djj



I dint get you. we create users for database and login's for instance right? How can a user of one database mapped to another database?

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-12-11 : 09:04:15
quote:
Originally posted by tkizer

You can't map multiple users from one database to a single login. It's a 1:1 relationship.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



For example if i need to give a particular database access to 10 users, then should i need to create 10 login's?


--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-12-11 : 09:09:54
quote:
Originally posted by tkizer

quote:
Originally posted by djj55

You can map a single user to multiple databases. Schema may cause problem if not handled.
You can also use Windows groups.

djj



sgondesi wants the opposite --> mapping multiple users in one database to one login.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Yes Exactly.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-11 : 13:00:43
quote:
Originally posted by sgondesi

quote:
Originally posted by tkizer

You can't map multiple users from one database to a single login. It's a 1:1 relationship.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



For example if i need to give a particular database access to 10 users, then should i need to create 10 login's?




It depends on the application. Are you providing direct access to the database? Can they use Windows authentication? Using Windows groups is preferred if the access is all the same. If the users are for the application, then why does everyone need direct database access? Typically user access is handled through application security where users are a row in a table, and then there's one SQL user that the application uses.

Please provide more details so that we can better answer your question.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-12-11 : 15:45:14
quote:

For example if i need to give a particular database access to 10 users, then should i need to create 10 login's?


It depends on the application. Are you providing direct access to the database? Can they use Windows authentication? Using Windows groups is preferred if the access is all the same. If the users are for the application, then why does everyone need direct database access? Typically user access is handled through application security where users are a row in a table, and then there's one SQL user that the application uses.

Please provide more details so that we can better answer your question.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
[/quote]

Yes i want to provide direct access to the database.
I want to use SQL Authentication.
Let suppose some 10 developers are working on a project which needs a particular database access. These can login into the instance and can see all the databases but they can access only the database to which they were given access to.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-12-11 : 15:48:29
Then create 10 SQL accounts and map their accounts to the one database.

You should be providing access via Windows accounts though. It simplifies things (uses current security context) and allows you to grant access via groups. If you use groups, you only need to add the group and then provide access to the group. Nothing else needs to be done by the SQL DBA. They just need to ensure that the group contains the right Windows users.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sgondesi
Posting Yak Master

200 Posts

Posted - 2013-12-12 : 04:22:02
quote:
Originally posted by tkizer

Then create 10 SQL accounts and map their accounts to the one database.

You should be providing access via Windows accounts though. It simplifies things (uses current security context) and allows you to grant access via groups. If you use groups, you only need to add the group and then provide access to the group. Nothing else needs to be done by the SQL DBA. They just need to ensure that the group contains the right Windows users.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/



Thanks for all the support.
I will work in the way that you have suggested.

--
Thanks and Regards
Srikar Reddy Gondesi,
BTECH-IT 2013 Passed Out,
Trainee for SQL Server Administration,
Miracle Software systems, Inc.
Go to Top of Page
   

- Advertisement -