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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy