February 24, 2006 at 3:44 am
I use the following script to add a login and set properties:
if not exists (select * from master.dbo.syslogins where loginname = N'TESQL01\MSS_BASEL2_READ')
exec sp_grantlogin N'TESQL01\MSS_BASEL2_READ'
exec sp_defaultdb N'TESQL01\MSS_BASEL2_READ', N'Basel2_T01'
exec sp_defaultlanguage N'TESQL01\MSS_BASEL2_READ', N'us_english'
GO
exec sp_addrolemember N'db_datareader', N'TESQL01\MSS_BASEL2_READ'
GO
There are no errors. the windowsgroup exists. When I open EM to check the result I see this login added. When I open the properties screen, I see on the first tab defaultDB = Basel2_T01, as expected. However, when I open tab Database Access, I see this login is granted access to master, and no access to Basel2_T01. When I click opn the Master row in this tab I receive a message, telling me this loggin is not found in the user collection.
When I use sp_addlogin, I receive a message telling me, the username is invalid, because it contains invalid characters. When I add this group with EM there is no problem.
I am at a loss. Obviously I am doing something wrong, but what?
Anyone can point me in the right direction?
Greetz,
Hans Brouwer
February 24, 2006 at 6:17 am
I'v run into things similar to this in the past. What I do when I want to find exactly how to add something via a script is run profiler on the server I am working on and run through the steps using enterprise manager. Then i can grab the scripts that Enterprise manager used through profiler and then run those scripts to re-do the job.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply