November 1, 2013 at 8:19 am
I was 'cleaning' up some security issues on database. I added users to a Group login ID used by the application because these users had been given db_owner. I created a role and granted create table, alter, delete to that role an added that role to the Group ID using sp_addrolemember.
It turns out the app creates temp tables with the users id in the table name and then deletes them. So the table name is [DatabaseName].[Domain\UserID].[TempTable] and they are getting the following message when the app tries to delete the table:
"Cannot drop the table 'TempTable' because it does not exist or you do not have permission".
I need a way to grant the needed permissions to the Group ID short of db_owner. Any ideas?
Thanks,
Sqlraider
November 5, 2013 at 2:47 am
are you creatogn dynamically sql script through app ?
which could be missing schema name in generated script
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 5, 2013 at 7:30 am
Bhuvnesh (11/5/2013)
...missing schema....
This is most likely the cause of the problem. 'dbo' has access to all objects in all schemas, so can do the DROP fine.
Do all users have their own schema, or is this a legacy system (ie pre-SQL2005). If they do, you could just grant the required permissions on their own schemas. It's not pretty, but it's better than DBO 🙂 and at least they'll only affect their own objects.
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
November 5, 2013 at 9:29 am
sqlmunkee (11/5/2013)
Bhuvnesh (11/5/2013)
...missing schema....This is most likely the cause of the problem. 'dbo' has access to all objects in all schemas, so can do the DROP fine.
Do all users have their own schema, or is this a legacy system (ie pre-SQL2005). If they do, you could just grant the required permissions on their own schemas. It's not pretty, but it's better than DBO 🙂 and at least they'll only affect their own objects.
This is a legacy system (scheduled to be re-written in SQL2008 in 6-8 months). I've re-instated their security as it was before I removed it. Once its re-written properly I'll remove their security and this will no longer be an issue.
Thanks,
Sqlraider
November 5, 2013 at 9:46 am
Love it when people did stuff "because it works", only to leave a timebomb for us later on 😛
Good effort getting as far as you did, though !!
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
November 6, 2013 at 1:03 am
Make habbit to include schema whenever playing with sql objects.it is good from "standard" as well as "safety" perspective
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply