Builtin\Administrator account removal

  • I am trying to remove the builtin\administrator account from my servers and create an account that will have read or read\write permission to the databases.

    My question is - is there a way to give a user account a read access to the database without using this query below which will only work if you have the user account in all the databases.

    EXEC sp_addrolemember 'db_datareader', 'SQLRO'

    Also does a user account need datareader access if the account has already datawriter access.

  • Be carefull when removing builtin\administrators.

    First of all grant your dba(-group) sysadmin (windows) rights for the sqlserver instance !

    for windows accounts, you'll have to grant serveraccess and access to the needed databases !

    e.g.

    USE [master]

    GO

    CREATE LOGIN [thedomain\TheWindowsUserAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[AdventureWorks]

    GO

    USE [AdventureWorks]

    GO

    CREATE USER [thedomain\TheWindowsUserAccount] FOR LOGIN [thedomain\TheWindowsUserAccount]

    GO

    USE [AdventureWorks]

    GO

    EXEC sp_addrolemember N'db_datareader', N'thedomain\TheWindowsUserAccount'

    GO

    A user does not have read permissions if they are not granted.

    So write access will not grant read permissions !

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thanks for your input.

    Yes I will grant my dba(-group) sysadmin (windows) rights for the sqlserver instance !

    what if I wanted to create a sql account and grant read access to this account to the database and not as a windows account, will this work and how can I accomplish this.

  • Almost exactly the same, but without FROM WINDOWS, and of course no domain, and also specifying a password:

    USE [AdventureWorks]

    GO

    CREATE LOGIN [SQLUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[AdventureWorks]

    GO

    USE [AdventureWorks]

    GO

    CREATE USER [SQLUser] FOR LOGIN [SQLUser]

    GO

    USE [AdventureWorks]

    GO

    EXEC sp_addrolemember N'db_datareader', N'SQLUser'

    GO

    "Got no time for the jibba jabba!"
    -B.A. Baracus

  • Thanks a million.

Viewing 5 posts - 1 through 4 (of 4 total)

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