Sql Activity log backup: logins

Showing posts with label logins. Show all posts

Tuesday, March 27, 2012

addlogin into linked server

Hi,
I need to develop a stored procedure to add or drop SQL server logins into
SQL servers based on some logics users give to me. My stored procedure is on
one server. I need to add logins to other servers through linked server.
Does anybody know how to run sp_addlogin to add SQL logins into linked serve
r?
Thanks a lot.BF (BF@.discussions.microsoft.com) writes:
> I need to develop a stored procedure to add or drop SQL server logins
> into SQL servers based on some logics users give to me. My stored
> procedure is on one server. I need to add logins to other servers
> through linked server.
> Does anybody know how to run sp_addlogin to add SQL logins into linked
> server?
Did you try
EXEC SOMESERVER.master.dbo.sp_addlogin=
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I just tried
exec wsdev.master.dbo.sp_addlogin 'test', 'test'
After this, I checked and saw the new login has been created. But I got the
following error message:
New login created.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
I cannot use this in production system with error message like this. Any
further suggestions?
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> Did you try
> EXEC SOMESERVER.master.dbo.sp_addlogin=
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I just tried
> exec wsdev.master.dbo.sp_addlogin 'test', 'test'
> After this, I checked and saw the new login has been created. But I got
> the following error message:
> New login created.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should be discarded.
> I cannot use this in production system with error message like this. Any
> further suggestions?
I was not able to repeat this. Exactly which versions of SQL Server do
you have on the two servers? Use serverproperty('ProductVersion') to
determine this.
Anyway, the error message looks like you are running SQL 2005. In such
case you can use EXEC() AT:
EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
If the target server is also running SQL 2005, you should not use
sp_addlogin at all, but rather CREATE LOGIN:
EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
Note however, that this command will fail, because SQL 2005 validates
the password according to Windows policy. This mainly happens on SQL 2003,
but SQL 2005 always frowns at username = password.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using SQL Server 2000. The exact version is 8.00.760.
I cannot use EXEC AT on my server. But it's good to know this new feature on
SQL 2005.
Now I am thinking open 2 database connections from my .NET application to
access 2 different servers.
Thanks.
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> I was not able to repeat this. Exactly which versions of SQL Server do
> you have on the two servers? Use serverproperty('ProductVersion') to
> determine this.
> Anyway, the error message looks like you are running SQL 2005. In such
> case you can use EXEC() AT:
> EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
> If the target server is also running SQL 2005, you should not use
> sp_addlogin at all, but rather CREATE LOGIN:
> EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
> Note however, that this command will fail, because SQL 2005 validates
> the password according to Windows policy. This mainly happens on SQL 2003,
> but SQL 2005 always frowns at username = password.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I am using SQL Server 2000. The exact version is 8.00.760.
> I cannot use EXEC AT on my server. But it's good to know this new
> feature on SQL 2005.
> Now I am thinking open 2 database connections from my .NET application to
> access 2 different servers.
I assumed that you were on SQL 2005, because I took for granted that
you were running this from a query tool, which in that case would
be Management Studio which uses SqlClient.
Moral: please always be specific with the environment you are using.
As for the solution, this what I have recommended in the first place,
if I had known that you were doing this from an application. Relying
on linked servers is fragile, since the definition of a linked server
could change or disappear completely.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

addlogin & grantdbaccess for a user

We are developing an application and we need to create a
user called "Application" which must be capable to create
new logins and grant permissions to they, so it must be
capable to run sp_addlogin and sp_grantdbaccess stored
procedures.
Can anyone help us?
Thanks.You can add the login to predefined server and database roles in order to
manage security:
--add security admin login
EXEC sp_addlogin 'Application', 'ApplicationPassword'
EXEC sp_addsrvrolemember 'Application', 'securityadmin'
GO
--grant security admin login database access
EXEC sp_adduser 'Application'
EXEC sp_addrolemember 'db_accessadmin', 'Application'
EXEC sp_addrolemember 'db_securityadmin', 'Application'
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Peter" <pedrojpz@.terra.es> wrote in message
news:015401c3c757$fd83eeb0$a601280a@.phx.gbl...

quote:

> We are developing an application and we need to create a
> user called "Application" which must be capable to create
> new logins and grant permissions to they, so it must be
> capable to run sp_addlogin and sp_grantdbaccess stored
> procedures.
> Can anyone help us?
> Thanks.

Thursday, March 8, 2012

Adding numerous login via script

Hi,

Can anyone point me in the direction of a script that incorporates sp_addlogin, which allows for adding 200 sql authentication logins from an excel spreadsheet, or a temporary sql table with the id info, containing the username, password, and def db?

I'm trying to avoid adding each new login one by one.

EXEC sp_addlogin 'username', 'password', 'default database'Thanks, BPH

A simple approach is to create an expression in your Excel spreadsheet that builds the Exec sp_addlogin line. Copy the formula down to all the rows, then copy/paste the value into Query Analyzer.

If you have loaded the columns into a database table, you can execute a Select statement that builds the lines. Again, copy and paste the results and execute them. Assume you have built a table call logintemp with username, pwd, and dbase columns:

Select 'Exec sp_addlogin ''' + username + ''', ''' + pwd + ''', ''' + dbase + ''''

From logintemp

You need to double the quote marks to yield a quote in the output string.

|||Thanks. I'll give that a shot.

Saturday, February 25, 2012

Adding logins via SSEUtil compared to SQL Server Management Studio

Hello all,

I am currently in the process of setting up an SQL Server Express installation that comes packaged with an application I have written. My problem is that I want to use SQL Server user management (not just windows users) which work fine if I set them up manually. I started writing a script that I have SSEUtil execute once the application is fully installed (a step in my installation script) which sets up the users and passwords etc. The script is similar to the following:

USE [DBName]
GO

EXEC sp_DropUser 'user1'
EXEC sp_DropUser 'user2'
EXEC sp_DropUser 'user3'
EXEC sp_DropUser 'user4'
GO

USE [master]
GO

EXEC sp_DropLogin 'user1'
EXEC sp_DropLogin 'user2'
EXEC sp_DropLogin 'user3'
EXEC sp_DropLogin 'user4'
GO

CREATE LOGIN user1 WITH Password = 'user1', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user2 WITH Password = 'user2', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user3 WITH Password = 'user3', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN user4 WITH Password = 'user4', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [DBName]
GO

EXEC sp_AddUser 'user1'
EXEC sp_AddUser 'user2'
EXEC sp_AddUser 'user3'
EXEC sp_AddUser 'user4'
GO

ALTER USER user1 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user2 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user3 WITH DEFAULT_SCHEMA = MySchema
ALTER USER user4 WITH DEFAULT_SCHEMA = MySchema
GO

REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 FROM MyRole
REVOKE ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 FROM MyRole
GO

EXEC sp_DropRole 'MyRole'
EXEC sp_AddRole 'MyRole'
GO

GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table1 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table2 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table3 TO MyRole
GRANT ALTER,DELETE,INSERT,SELECT,UPDATE ON MySchema.Table4 TO MyRole
GO

EXEC sp_AddRoleMember 'MyRole','user1'
EXEC sp_AddRoleMember 'MyRole','user2'
EXEC sp_AddRoleMember 'MyRole','user3'
EXEC sp_AddRoleMember 'MyRole','user4'
GO

Now if I run this script from within SQL Server Management Studio it executes perfectly. The logins add, the role is added, each user is added to the database logins and assigned to the role, the schema is set correctly on each user.

Then when I try to run the exact same script from the SSEUtil application (SSEUTIL -s PCNAME\Instance -run USERS.SQL), it processes everything, except the Logins.

This is frustrating as it means to install for a client I would need to either get them to open the management console and run the script from there, or I have to go to site just to setup users.

Am I on the right track? Or is there another way to automate the adding of Logins?

Thanks in advance,

DSXC

Just an update.

I did a check within my database and found that the sys.syslogins has the users (when I do a select from the view) but they just don't work. The only difference I can see between my scripted login I created and the SA user is the flag for sysadmin, but thats understandable as these users are not to be sysadmins.

Is there another table that actually enables the login?

DSXC

|||

I noticed a lot of people using the SQLCMD.EXE instead of the SSEUtil.EXE I was using so I thought I'd give it a try.

Lo and behold... it works!

Talk about crazy... oh well. Just so everyone knows, use the SQLCMD.EXE over SSEUtil.exe... gah!

EDIT: I didn't mention the command line to run it...

SQLCMD.EXE -i MYSCRIPT.SQL

Hope that helps.

DSXC

|||

Hi DSXC,

Sorry to have missed this thread earlier, I can shed some light on what you're seeing.

SSEUtil.exe is an unsupported tool primarily used for troubleshooting User Instance problems, it is not meant to be used as a general solution for runing scripts, nor is it licensed to be deployed with your application. Additionally, the default mechanism of SSEUtil works against the User Instance, not the parent instance, so my guess is it was not working the way you think it was.

SQLCmd is the general scripting utility that is installed with all copies of SQL 2005 and is exactly the tool you should be using for what you wish to accomplish, but you've already discovered that.

Mike

|||

Hi Mike,

Thanks for your response. It makes a bit more sense now.

I found the SSEUtil app with a search on attaching the database to my SQL Server so I guessed it would have worked running scripts against it also. I just noticed that the SSEUtil doesn't attach my database correctly either so I've now transferred over to using another SQL script.

Again thanks for your response.

DSXC

Adding logins through Query Analyzer

Ok, this one is way out there so I'm not sure anyone will have witnessed this
before.
On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure. If I reset the password,
it works fine. If I use the account to connect right after I set it up, it
works fine.
Has ANYONE ever witnessed this? It's the weirdest thing.
Thanks.Also, if I go into Query Analyzer and run sp_password to change the password
again, it will only accept the old password as being NULL.|||Passwords usally don´t change or expire on their own (In SQL Server they
will apply to domain policies, but not in 2000) ?
If you are logged in as a sa, you can reset the password without knowing the
old one by passing NULL to the SP.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"Ana" <Ana@.discussions.microsoft.com> schrieb im Newsbeitrag
news:AF003637-D917-4F54-800E-A36EEE7B8FF0@.microsoft.com...
> Also, if I go into Query Analyzer and run sp_password to change the
> password
> again, it will only accept the old password as being NULL.|||Yes, I know that being logged in with sa will allow me to change the password
without knowing the old one...I am having to do this now. My point was that
this is the only way it will allow me to change the password. I was just
verifying that SQL Server seriously doesn't know what the original password
is. I can't login with it, and I can't change the password with the original
password.|||> SQL Server seriously doesn't know what the original password
> is. I can't login with it,
Well, if it has changed, this makes sense. If it has not, then surely you
are spelling it wrong, or transcribing from the wrong yellow sticky on your
monitor.
In summary, one of the following is true:
(a) the password has changed
(b) you are not using the correct password
SQL Server doesn't just forget what the password is.
> I can't change the password with the original password.
Why do you need the original password to change it? Log in as a local
administrator and issue:
EXEC sp_password NULL, 'oldPassword', 'sa'
Now try logging in as sa again, with the old password that SQL Server
"doesn't know"... a penny says it works.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.|||Ok, I guess I need to spell this out...
First of all, I said this has happened on several occasions; it's not just a
one-time slip up of either the fingers or the "yellow sticky".
Second, we are a large company with several SQL Servers and from time to
time, we have the need to set up a uniform service account on all of our
servers that one application can use. To do this, we use sp_addlogin and
sp_addsrvrolemember or sp_adduser/sp_addrolemember statements and run the
exact same set of statements on each server. Oftentimes, due to project
timelines and delays on someone else's end, we set up these service accounts
and they don't get used for weeks. This is the ONLY time we see the issue of
not being able to login with the documented information (when the account has
sat for an extended period of time without ever being used).
Third, when we set something like a service account up on multiple servers,
the first server is always tested to make sure that the documented login
information works to authenticate to the server. Then the remainder of the
accounts are set up, using the same statements that were used to create the
account on the first server.
Fourth, we are a Sarbanes-Oxley affected operation. What this means is
there are only two people in our company (myself and the other SQL Server
DBA) that have sysadmin accounts to all the servers and no one besides us
knows the passwords to any of dbo service accounts (of which there are few).
What this means is, only we would be able to change the passwords for the
service accounts and we have far more important things to do than to sabotage
ourselves.
I hope this offers a more complete explanation of what we're seeing.|||> we have far more important things to do than to sabotage ourselves.
I wasn't suggesting this. But your explanation of this just doesn't add up.
Your server suddenly doesn't know its own SA password?
> I hope this offers a more complete explanation of what we're seeing.
This doesn't explain to me how SQL Server suddenly "forgets" the SA
password. As I said before, Sarbanes-Oxley, perfect people, what have you,
one or both of the following is true:
(a) the password has changed
(b) you are not using the correct password|||Where did I ever say that it was forgetting the SA password'
"On several occasions, I have noticed that if I set up a SQL Server level
login and grant permissions to this login (either server-level or database
roles, doesn't matter which) through Query Analyzer and then don't use the
account right away (say for a couple of weeks or more), if I then try to use
this login it fails due to authentication failure."|||Sorry, I was having trouble deciphering this:
"Yes, I know that being logged in with sa will allow me to change the
password
without knowing the old one...I am having to do this now. My point was that
this is the only way it will allow me to change the password. I was just
verifying that SQL Server seriously doesn't know what the original password
is. I can't login with it, and I can't change the password with the
original
password."
Maybe if you had mentioned the actual login name you were using, there would
have been less confusion over which login was actually affected.
In any case, change my previous post from "the SA" to "any", and my point
remains the same. SQL Server doesn't just lose passwords, something else is
causing this.|||I'm guessing if you'd read the original posting and then followed the chain
through, maybe you wouldn't have been so confused. I didn't mention any
account names because this has happened several times to more than one
account.
In any case, my question to your comment "SQL Server doesn't just lose
passwords, something else is causing this" is: Have you ever tried doing
what I've described as our process (create a SQL Server level login and grant
it permissions to whatever through Query Analyzer, DON'T USE THAT ACCOUNT for
at least a few weeks, then try to use it to connect to SQL Server after
several weeks have passed) and can verify that this definitely would never
happen?
Also, I'm telling you quite explicitly, that we have not changed the
password and we are using the password that was set.
Yes, I know this sounds extrordinary, but that's why I've put a posting out
about it.|||"Maybe if you had mentioned the actual login name you were using"
Sheesh! It's always entertaining to watch people respond to posts without
reading the OP's message. Lest I'm on drugs I couldn't see anywhere where Ana
specified the SA account, indeed the phrase used was "set up a SQL Server
level login". If you want to assume the OP meant SA then that's your problem
laddy'o... And you know what they say about assumption: makes an ass out of u
and me|||Hey laddy'o, why don't you look at the thread and see where I came in.
If you have some useful advice for the OP, or even a useful contribution to
the thread, why don't you submit it, instead of calling people names.
Sheesh is right.
--
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Scrat" <Scrat@.discussions.microsoft.com> wrote in message
news:E8C30EA4-E3C9-4595-B1FD-8C7C66D277BE@.microsoft.com...
> "Maybe if you had mentioned the actual login name you were using"
> Sheesh! It's always entertaining to watch people respond to posts without
> reading the OP's message. Lest I'm on drugs I couldn't see anywhere where
> Ana
> specified the SA account, indeed the phrase used was "set up a SQL Server
> level login". If you want to assume the OP meant SA then that's your
> problem
> laddy'o... And you know what they say about assumption: makes an ass out
> of u
> and me|||Okay, good luck finding the ghost in the machine.
I'll be looking forward to seeing the actual solution. (Never mind your
admission that, yes, somebody DID change the password or re-create the login
after all.)
Heard of profiler? Might want to have it scan for events like sp_addlogin,
sp_droplogin, and sp_password.
A
"Ana" <Ana@.discussions.microsoft.com> wrote in message
news:3F2CDB76-49F8-4BE9-996A-4FEF93B0C49D@.microsoft.com...
> I'm guessing if you'd read the original posting and then followed the
> chain
> through, maybe you wouldn't have been so confused. I didn't mention any
> account names because this has happened several times to more than one
> account.
> In any case, my question to your comment "SQL Server doesn't just lose
> passwords, something else is causing this" is: Have you ever tried doing
> what I've described as our process (create a SQL Server level login and
> grant
> it permissions to whatever through Query Analyzer, DON'T USE THAT ACCOUNT
> for
> at least a few weeks, then try to use it to connect to SQL Server after
> several weeks have passed) and can verify that this definitely would never
> happen?
> Also, I'm telling you quite explicitly, that we have not changed the
> password and we are using the password that was set.
> Yes, I know this sounds extrordinary, but that's why I've put a posting
> out
> about it.|||Hey, you wanna jump in a thread mid point and start making assumptions then
go right ahead. Just makes you look kinda foolish is all...
Given how short the thread is it behooves one to read the initial post - and
thus save a lot of to-ing and fro-ing with the OP.
Normally I'd have said nowt but your arrogance annoyed me somewhat. A simple
apology to the OP would have helped 100%.
So, to add to the thread, I think what Ana's getting at is that this *is*
weird behavior & she mentioned that it was a couple of weeks later before the
problem transpired. You meant to leave Profiler running 24x7 to find it? Even
the blackbox prolly don't help since it'll rollover every 10MB.
Rather than picking at the OP, trying reading and helping. Or does being an
MVP put you above us mere-mortals?|||"AB - MVP" wrote:
> Okay, good luck finding the ghost in the machine.
> I'll be looking forward to seeing the actual solution. (Never mind your
> admission that, yes, somebody DID change the password or re-create the login
> after all.)
> Heard of profiler? Might want to have it scan for events like sp_addlogin,
> sp_droplogin, and sp_password.
> A
I give up. This is precisely why I never post anything; no one wants to
actually help the person having issues, but they all want to try to look
smarter than everyone else.
AB, you seem to have a fairly closed-minded approach to helping those with
sincere questions. This is clearly demonstrated by the fact that you
couldn't even answer my last question and the fact that you thought it was ok
for you to waste my time by not reading the entire thread before you started
pointing fingers. You are arrogant and rude and how you ever EARNED a title
such as MVP is beyond me.|||> Rather than picking at the OP, trying reading and helping. Or does being
> an
> MVP put you above us mere-mortals?
Of course, no matter how hard I try to help, there's some ignorant a-hole
for whom it's not good enough.
Ciao.|||Ana,
I did some googling for anything close to your problem. Dunno if this helps,
but...:
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/browse_frm/thread/4ebde7376a0d78a5/8e40673af9b17e87?q=sp_addlogin+query+analyzer&rnum=3&hl=en#8e40673af9b17e87|||Hi Ana,
I have read your post.
Two quick suggestions (2 is the only one I expect to be relevant)
1) I have seen sometimes when a backup is restored that the users can get
'orphaned'. They appear in both the master and the 'restored' db but the
login does not work. Slim chance that this is the case but who knows?
2) The other thing I thought could be helpful is to place a trigger on the
password columns to log to a new table if any changes do actually occur
during the two week schedule and who did it or if somehow there is some bug
in SQL (not that they ever happen of course).
Good luck I wish it was a better suggestion but maybe it will help.
Mike LVP ;-)|||Mike,
Thanks for your response. The first suggestion doesn't really apply here
because we're not restoring users from another database, we're simply
creating new users. The second option might work. I'll discuss this with my
fellow DBA and see what he thinks.
Luckily, we don't do these "mass single user creations" all the time.
Thanks again!
Ana

Adding logins and permissions

Hi,
I am at a company with 18 employees and I have 11-12 databases in SQL server. I can't seem to give logins and permissions to groups. Is there a simpler way, or do I have to add every single employee to each database and give permissions?
You may create roles (it's a group analogue) within databases and then assign your employees to these groups. There are no serverwide groups except of builtin server roles.

Sunday, February 19, 2012

Adding Domain Local Group as SQL Logins

Adding Domain Local Group as Logins

Hi,
I can not found Domain Local Group when I was adding SQL Logins. But, I foun
d them when using NTFS permission on the same computer. My Domain is 2000 Na
tive mode, SP3 was installed on the SQL Server and everyone group was added
in Pre-windows 2000 Acces
s Group. What's the problem? Help plz.
Rgds,
Harry"HarryNg" <anonymous@.discussions.microsoft.com> wrote in message
news:180922DE-A85E-4D3D-8AB4-ECF10EED154B@.microsoft.com...

> I can not found Domain Local Group when I was adding SQL Logins. But, I
found them when using NTFS permission on the same computer. My Domain is
2000 Native mode, SP3 was installed on the SQL Server and everyone group was
added in Pre-windows 2000 Access Group. What's the problem? Help plz. <
This may be by design. A very simple workaround, create a local group on
your server running SQL Server, then add your domain global group into the
local group on your server. Then, add the (server) local group to the SQL
Server login and give it the proper db permissions...
Steve|||This was a bug that was fixed.
825042 FIX: SQL Server Jobs That Are Owned by Non-sysadmin Users May Not
Start
http://support.microsoft.com/?id=825042
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Thursday, February 9, 2012

Adding Active Directory login (ca vs. samaccount)

I am having some issues adding user logins to SQL Server. When I
search for users in Active Directory from Security->Logins->New Login,
I can search for and add users using the short name (samaccount, ex
domain\abcde). However, when I search for users using their CA name
(ex. domanin\firstname.lastname), the user is not found.

Is there some setting that needs to be activated in SQL Server to see
the CA name as well as the samaccount name, or is this an issue that
must be resolved on the domain controller.

I would appreciate any insight.

Thanks,

MattHi

SQL Server 2000 only understands "domain\abcde" and not abcde@.domain or CA
names.

Sorry, but can't do much about it.

----------
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/

"mattsh" <matt_sh2001@.yahoo.com> wrote in message
news:1116503684.651798.63340@.g43g2000cwa.googlegro ups.com...
>I am having some issues adding user logins to SQL Server. When I
> search for users in Active Directory from Security->Logins->New Login,
> I can search for and add users using the short name (samaccount, ex
> domain\abcde). However, when I search for users using their CA name
> (ex. domanin\firstname.lastname), the user is not found.
> Is there some setting that needs to be activated in SQL Server to see
> the CA name as well as the samaccount name, or is this an issue that
> must be resolved on the domain controller.
> I would appreciate any insight.
> Thanks,
> Matt|||OK, but for some people (like myself) my "long name", say
domain\firstname.lastname (what I believe is our CA name) shows up. My
short name domain\ablastname (what I believe is the samaccount name)
can also be chosen as a login. But for other people, the only thing
that I can access is the domain\cdlastname user name. The
domain\firstname.lastname does not show up when I do a search from SQL
Server.

If it works for me, I figure it should work for others. I just can't
figure out what I need to do so I can add others "long name" (CA).

Thanks again,

Matt

Tuesday, March 27, 2012

addlogin into linked server

addlogin & grantdbaccess for a user

Thursday, March 8, 2012

Adding numerous login via script

Saturday, February 25, 2012

Adding logins via SSEUtil compared to SQL Server Management Studio

Adding logins through Query Analyzer

Adding logins through Query Analyzer

Adding logins through Query Analyzer

Adding logins and permissions

Sunday, February 19, 2012

Adding Domain Local Group as SQL Logins

Adding Domain Local Group as Logins

Thursday, February 9, 2012

Adding Active Directory login (ca vs. samaccount)

Sql Activity log backup

Blog Archive

About Me