Post reply

sql users

  • April 16, 2010 at 10:59 am

    #217887

    Hi All,

    I am having a problem when i create a new database. Please see below for the problem.

    What I am doing is...

    1 - Creating a database (Create Database [mydb];)

    2 - Creating a login (CREATE LOGIN [naimulah] WITH =N'naimulah', DEFAULT_DATABASE=[mydb], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;)

    3 - Granting access to the database (CREATE USER [naimulah] FOR LOGIN [naimulah] WITH DEFAULT_SCHEMA=[dbo];)

    4 - Making the user db_owner of of that database (EXEC sp_addrolemember 'db_owner', 'naimulah';)

    Once I have done these 4 steps, I undo them by

    1 - sp_droprolemember

    2 - DROP USER

    3 - DROP LOGIN

    4 - DROP DATABASE.

    After all this, I create a new database 'mynewdb' manually using the SQL Server Management Studio. Now after creating 'mynewdb' database what I find is that there is a user named 'naimulah' under the security folder of the database. I don't get it how the sql server has created this user against this database. I can't see any instance of this user anywhere in the Management Studio and in the master..sysusers view.

    Please help me out here as how to get rid of this user in the new databases I create. An explanation of why this is happening would be much appreciated.

    Thanks in advance

    naimulah

  • April 16, 2010 at 11:21 am

    #1153038

    a new database is really a copy of the model database.

    if you add user,sroles, functions,tables,procs or anything else to the model database, all new databases inherit them when they get created as "new".

    take a look at your model database's security folder, and I think you'll find that user and role was added there.

    remove it from model and your issue should go away.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply