• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 993
  • Last Modified:

grant user 'select' permissions - sql 2008

i have a db on a sql2008 server
i want to give a windows authentication user 'select' permissions, or 'read' permissions on this db

how do i ?
0
sknoll84
Asked:
sknoll84
  • 4
  • 3
  • 3
  • +4
1 Solution
 
BardobraveCommented:
You can play with windows and sql authentications from "Security" -> "Logins"

You can add a new windows authentication and, from properties, assign it to a determinate database role or specify the exact permissions you want to assign to it.
0
 
David KrollCommented:
In SQL Management Studio, right click on the DB, go to properties.  Then go to permissions, add the username or role and check grant on "select".
0
 
Daniel_PLDB Expert/ArchitectCommented:
Try this:

CREATE LOGIN <name of Windows User> FROM WINDOWS
GO
USE <yourdbname>
GO
CREATE USER <user name> FOR LOGIN <login name>
GO
EXEC sp_addrolemember db_datareader, <user name>
GO

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sventhanCommented:
follow the above post and add "db_dataread" role to the win auth user.
0
 
sammySeltzerCommented:
Open SSMS, on the explorer window, expand Security, right-click on user and select add new user.

Select Windows Authentication mode and add new user DomainName\Username

Once you have done this code:

GRANT SELECT ON [tableName] TO [user]

Hope this helps
0
 
sknoll84Author Commented:
CREATE LOGIN domain\user FROM WINDOWS
GO
USE xxLL1_Production
GO
CREATE USER domain\user FOR LOGIN domain\user
GO
EXEC sp_addrolemember db_datareader, domain\user
GO

throwing errors at the \
at first i didnt have the \ and it complained about needing domain
0
 
sknoll84Author Commented:
i like @sammySeltzer approach

i'm logged in as sa and added the domain user to security>logins

GRANT SELECT ON xxLL1_Production TO user

returns

Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'xxLL1_Production', because it does not exist or you do not have permission.
0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Try this...
use master
go

--Groupe pour les accès en lecture sur les bases VMAT
if not exists (select name from syslogins where name = 'YOURDOMAIN\YOURLOGIN')
begin
	create login [YOURDOMAIN\YOURLOGIN] from windows;
end

use [YOURDB]
go

if not exists (select 1 from sysusers where name = 'YOURDOMAIN\YOURLOGIN')
     begin
	CREATE USER [YOURDOMAIN\YOURLOGIN] FOR LOGIN [YOURDOMAIN\YOURLOGIN];
     end

Open in new window

0
 
sventhanCommented:
use mydb
go

exec sp_addrolemember db_datareader, MYUSER
go
exec sp_addrolemember db_datawriter , MYUSER
go
0
 
sventhanCommented:
GRANT SELECT ON dbo.xxLL1_Production TO user
0
 
sknoll84Author Commented:
GRANT SELECT ON dbo.xxLL1_Production TO jdoe

Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'xxLL1_Production', because it does not exist or you do not have permission.


logged in as sa
user exists under security>logins

not have elite dba skillz so these other solutions are over my head.  much love.
0
 
Daniel_PLDB Expert/ArchitectCommented:
If xxLL1_Production is database and jdoe is a login:
USE xxLL1_Production
GO
CREATE USER jdoe FOR LOGIN jdoe
GO
EXEC sp_addrolemember db_datareader, jdoe
GO

Open in new window

0
 
sknoll84Author Commented:
@daniel, this is the output

Msg 15007, Level 16, State 1, Line 1
'jdoe' is not a valid login or you do not have permission.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role 'jdoe' does not exist in this database.
0
 
Daniel_PLDB Expert/ArchitectCommented:
Ok, lets start over. Do you have domain or you are creating login for local windows account?
In following code please change domain to domain name or if no domain exists to your computer name and login name to existing windows account, e.g.
LAPTOP\jdoe or CORP\jdoe
USE [master]
GO
CREATE LOGIN [domain\login_name] FROM WINDOWS
GO
USE [xxLL1_Production]
GO
CREATE USER [domain\login_name] FOR LOGIN [domain\login_name]
GO
EXEC sp_addrolemember N'db_datareader', N'domain\login_name'
GO

Open in new window

0
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
oops forgot the read priviledge...please run this

hope htis helps
use master
go

if not exists (select name from syslogins where name = 'YOURDOMAIN\YOURLOGIN')
begin
	create login [YOURDOMAIN\YOURLOGIN] from windows;
end

use [YOURDB]
go

if not exists (select 1 from sysusers where name = 'YOURDOMAIN\YOURLOGIN')
     begin
	CREATE USER [YOURDOMAIN\YOURLOGIN] FOR LOGIN [YOURDOMAIN\YOURLOGIN];
     end

exec sp_addrolemember N'db_datareader', N'YOURDOMAIN\YOURLOGIN'
go

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
  • 3
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now