SQL Server Security 2005+ - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Server Security 2005+

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-09 : 13:17:28

OK...I have been working with granting to a ApplicationID for all of the apps I support for so long...so I just grant to it

So when I created a rols in 2k8, gave the role datareader, and put users in the role, WHY can't the read from the tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-08-09 : 13:28:50

Check if they are being denied access through other things as denial takes precedence.

But what you've described is how you grant access via roles, so it should work.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-09 : 13:52:58

same /:sh'eet/ :) happend to me when migrating to sql2008R2. therefore i had to set permission on each object for each role/group.

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-09 : 14:10:12

Maybe I'm assigning the Role datareader the wrong way...

When I script it, I don't see the assignment to the datareader role

I did notice that I forgot to add the users, so that's a BIG problem...duh...I had added them, the created the role as schemas owned by this role...which I realized is a BIG mistake...(Why do they have this)

So I dropped the role...now I added everyone..when I script it, I get



USE [PLS]
GO
/****** Object:  User [PRUDENTIAL\X150753]    Script Date: 08/09/2010 14:04:02 ******/
CREATE USER [PRUDENTIAL\X150753] FOR LOGIN [PRUDENTIAL\X150753] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  Role [app_admin]    Script Date: 08/09/2010 14:04:02 ******/
CREATE ROLE [app_admin] AUTHORIZATION [dbo]
GO
/****** Object:  Role [app_user]    Script Date: 08/09/2010 14:04:02 ******/
CREATE ROLE [app_user] AUTHORIZATION [dbo]
GO
/****** Object:  Role [PRUBKREC_Reader]    Script Date: 08/09/2010 14:04:02 ******/
CREATE ROLE [PRUBKREC_Reader] AUTHORIZATION [dbo]
GO

So I don't see datareader, and what the hell is app_user and app_admin???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources