Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We're running a SQL Server 2000 instance with users tied to Windows accounts. Many of the usernames have spaces in them and that is causing us some problems now. We would like to be able to rename them to get the spaces out, but I'm unable to figure out how this is done. I would love some help on this.

share|improve this question
1  
Would an answer to workaround spaces help? And do the AD accounts have spaces in? –  gbn Jun 16 '11 at 17:00
    
They do, yes. Mine for example is DOMAIN\Andy Soell A workaround is fine as long as my linked Access application won't complain when it tries to create views. If I try to create a view, it tries to name it "Andy Soell.viewname" and that space is causing me problems. –  andy Jun 16 '11 at 17:04
add comment

1 Answer

up vote 3 down vote accepted

Some notes

  • When you create a login, then the name must match AD exactly
    EXEC sp_grantlogin 'DOMAIN\Andy Soell
  • When you map the login to a database user, it can be renamed
    EXEC sp_grantdbaccess 'DOMAIN\Andy Soell', 'Dilbert'

The 2nd bit will fix your issue with implied schema. This happens if don't qualify schema (eg dbo) because SQL Server 2000 has pretty much no user-schema separation like later versions

Note: you can't rename users in SQL Server 2000 IIRC: need to run sp_revokedbaccess then sp_grantdbaccess. If you own objects because of your implied schema then you'll need sp_changeobjectowner. I think SQL EM will prompt you.

That is, you should always qualify schema in SQL Server.

CREATE TABLE dbo.viewname...

SELECT * FROM dbo.myTable

EXEC dbo.MyProc

Then no need to change the users

share|improve this answer
    
That worked perfectly, thanks so much! I know that it would be preferable to save view as dbo.viewname, however it seems that Access will still try to save new views on a linked SQL Server with the user's name, even if they are flagged as a database owner. Hence my problem htere. –  andy Jun 16 '11 at 19:01
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.