May 1, 2008 at 1:53 pm
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.
May 1, 2008 at 2:52 pm
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
May 1, 2008 at 7:44 pm
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.
May 2, 2008 at 6:52 am
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
May 2, 2008 at 2:50 pm
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