December 1, 2009 at 1:55 pm
Hi everybody,
I'm creating a login/user and granting access to a DB in a SQL Server 2005 by several ways (Management Studio, sql script) but I always get the same issue in that specific DB.
The sql script is as follows:
USE [master]
CREATE LOGIN [MyADDomain\MyWindowsUser] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
USE MyDB
GO
CREATE USER [MyADDomain\MyWindowsUser] FOR LOGIN [MyADDomain\MyWindowsUser]
ALTER USER [MyADDomain\MyWindowsUser] WITH DEFAULT_SCHEMA=[dbo]
GO
GRANT SELECT TO [MyADDomain\MyWindowsUser]
GRANT EXECUTE TO [MyADDomain\MyWindowsUser]
GRANT INSERT TO [MyADDomain\MyWindowsUser]
GRANT UPDATE TO [MyADDomain\MyWindowsUser]
GRANT DELETE TO [MyADDomain\MyWindowsUser]
GO
If I: right click on MyDB -> Properties -> Permissions -> Select the user -> click Effective Permissions
In "Effective Permissions" window I should get in the Permission Column:
CONNECT
DELETE
EXECUTE
INSERT
SELECT
UPDATE
However, I'm only getting a sort of ALTER's CONTROL, CREATE's and another Permissions, but never the INSERT, UPDATE and DELETE permissions.
I have tried everything even deleting/creating the Login from Management Studio and/or sql script. And the problem is specific in that DB because I have tried the same with another DB and I get the expected results.
Please could you help me to fix this issue?
Thanks very much in advance!!
😎
December 1, 2009 at 3:53 pm
I think your problem is you seem to be misusing the grant statement. I checked the syntax to be sure and what you appear to be doing is only setting the database level rights. NOT any object rights. Also I am unsure why you would grant rights this particular way it would be easier to add the user to the fixed database role db_owner since that is effectively what you are doing.
I would suggest reviewing the grant database permissions and the the grant object permissions articles for greater clarification.
CEWII
December 1, 2009 at 4:08 pm
I thought that GRANT EXECUTE would give you permissions to all stored procs under a particular schema, but I'm not sure about insert and update.
If you want rights to all objects, there are db_datareader, db_datawriter roles. However most people seem to find that they actually don't want rights to all objects. at some point you'll want to change things and it becomes a mess. You would be better off creating your own role and granting rights to each object to that role. Then add users to the role
December 1, 2009 at 4:30 pm
Check to see if there any DENY's in that database.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 1, 2009 at 4:32 pm
Elliott W (12/1/2009)
...Also I am unsure why you would grant rights this particular way it would be easier to add the user to the fixed database role db_owner since that is effectively what you are doing....
No, this is well below [db_owner], it's more like [db_datareader]+[db_datawriter]
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 2, 2009 at 7:06 am
Thanks everybody for your responses
I don't wish to give db_owner role as I don't want the user to change/modify the objects structure (tables, sp, fn). But anyway I have also tried giving that role and as db owner the user still had denied INSERT, UPDATE and DELETE access to all the tables in the DB. (I also tried giving the db_datawriter role with the same results).
An additional thing that I tried and "worked" was giving the Sysadmin server role to that login, but as you know, it is not a best practice to give those rigths to common users.
I guess that my DB is corrupt in some way and I don't know how to fis this issue. Meanwhile I'm going to leave the sysadmin server role for that Login.
Please if you have any additional idea let me know... thanks!!!
December 2, 2009 at 7:47 am
It's not corrupt. Likely you have permissions that are conflicting, and there are DENYs that override the GRANTs.
Make a new login, give it permissions to a specific object, INSERT to a table, and insert there. If that doesn't work, perhaps something is wrong, but leaving it as sysadmin is a mistake and it's asking for trouble. Somenone can easily change structures with that, or even drop a database.
December 3, 2009 at 12:03 pm
RBarryYoung (12/1/2009)
Check to see if there any DENY's in that database.
As Steve and I have both said, this is what you need to look for.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 19, 2011 at 6:06 am
You need to jointly consider conflicting permissions as well as any DENYs.
Firstly, establish if your login/account is a member of other windows group logins.
The lowest privilege level will be the one that takes effect.
You will probably find that one of the groups has a DENY assigned.
So it's either get SA/DBA to remove you from one of the groups or remove the DENY.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply