June 21, 2004 at 8:15 am
I'm a new SQL Server DBA and need a little help. I've been trying to change the security of a few databases. I've been working with one user for the last couple of hours and am at my witts end. I give the user (using Windows authentication) datawriter and datareader access to database that I am working on and the user can't do anything within the database. Can't edit, create anything. (Stored Procs, Views, Functions, etc.) I'm trying to set this user up as a "developer" type user. Where he can't change the database design (adding or editing tables, users, etc.) Like I said above, as soon as I give him datareader and datawriter permisions only, he can't do anything. Is there something that I'm missing.
Thanks in advance for all your help.
Newbie
June 21, 2004 at 9:19 am
Hi,
Is this user a member of another group or role? The permissions may be restricted based on his group membership. Did you by any change restrict access by applying db_denydatareader and db_denydatawriter to any SQL server logins that are Windows groups? Like Authenticated Users or Everyone? Don't think it is funny. I had to help somebody with a similar case Friday. Another typical mistake is when you want to give all access to a role and check all checkboxes including 2 at the bottom of the list for db_deny... things.
I would try the following to troubleshoot the issue: give this person db_owner right and check if he can access the db. Give permissions to another Windows login who is not sysadmin If those not-sysadmin people could not connect then there is a Deny somewhere in the role permissions
Yelena
Regards,Yelena Varsha
June 21, 2004 at 9:38 am
Thanks for the quick response.
Just so I could start testing, I created another user in Active Directory (sqluser). The only group in AD the new user is apart of is "Domain Users." Once done, I added the user in the SQL Server logins under the database that I am testing and gave the login "db_datawriter" permissions only. I also have not assigned any roles to this user.
Once added in both AD and SQL Server I registered our SQL test server under the new users login, verified that the user was registered in the database that I am working with and still no access to any stored procs, tables, views, etc.
Once again, thanks for all the help
Newbie
June 21, 2004 at 9:48 am
Also, if I assign the user the System Administrators role, the user can access everything without a problem.
June 21, 2004 at 9:51 am
(1) What is the exact error message?
(2) Does he log in from another computer?
(3) There are lots of situational "Special Identity" groups in Windows: Everyone,Authenticated Users, Batch, Dial-Up, Interactive, terminal server User and many more. You do not specify a membership in those groups, it is by user actions. Check in your database permissions on the database if access is denied to anyone at all
(3) can this user see SQL Server at all? Was it a message "SQL Server does not exist or access denied" ? In this case it is a network protocol problem
Regards,Yelena Varsha
June 21, 2004 at 9:53 am
It does look like Deny somewhere. Sysadmin membership overrides "Deny"
Regards,Yelena Varsha
June 21, 2004 at 10:01 am
(1) If the user opens up a type "User" stored proc, everything is grayed out. If the user trys to open a type "User" table, I see the error "SELECT permission denied on object 'table_name', database 'db_name', owner 'dbo'
(2) I have another laptop next to me where the "sqluser" is logged into. I also have the user connecting to the database using "Windows Authentication"
(3) In the database permissions, no one is denied (or assigned) to any of the databases. All of the check boxes for all of the users are blank. Is it as simple as this? Please say no, I'll feel pretty stupid
(4) Yes, the user can Register the SQL Server without error, just can't do anything once registered.
Thanks again for all of your help. It is very appreciated.
June 21, 2004 at 10:07 am
I gave the user permissions on the under the databse properties. I can finally open up a stored proc and its not grayed out. But I can't change it. The same thing goes for tables. Do I need to give access to each table/stored proc to the user? If I do, is there an easier way to do that rather than going to the permissions of every object in the database.
Thanks
June 21, 2004 at 11:00 am
1) User should be a member of db_ddladmin to create, update procedure.
2) If you want to give execute permissions to all stored procedures in a database to a user, you can use the following procedure. Add the procedure in master database and execute it locally from the database. You can create a role Execute_StoredProcedure and then run the following procedure locally from the database.
/*
This procedure grants Execute permissions to all procedures on a database to the role desired.
The default role is Execute_storedprocedure
*/
CREATE procedure sp_grant_permissions_proc @role varchar(100)='Execute_StoredProcedure' as
declare curname cursor for select name,user_name(uid) from sysobjects where xtype = 'P'
declare @procname varchar(100)
declare @username varchar(100)
open curname
fetch next from curname into @procname, @username
while @@fetch_status = 0
begin
declare @sql varchar(300)
set @sql = 'grant execute on ' + @username + '.[' + @procname + '] to Execute_Storedprocedure'
exec(@sql)
fetch next from curname into @procname, @username
end
close curname
deallocate curname
GO
June 21, 2004 at 11:53 am
I noticed that the first time you said "datareader and datawriter" the second time in your reply you say "datawriter only". Yes, datawriter is only for writing, not for selecting. Select permision will not be given. Yes, you have to execute stored procedures and users should have Execute permission on stored procedures and the script posted by sa24 looks like a very good script.
sa24 is so right about create/modify permissions for objects: users should be in db_ddladmin role.
If the checkbox is blank it means the user was not explicitly granted or denied permissions. It means if you grant him a permission it should work
Regards,Yelena Varsha
June 21, 2004 at 12:43 pm
Thanks for all the help. I think I got it.
The only question that I have is about the db_ddladmin role. Why do you suggest I add this to all the users. If I do add this to the database users will they be able to create new databases/tables etc. The only person I want to be able to create new databases/tables/etc. is myself.
I only want the developers to be able to write stored procs/views/functions, update/insert into tables etc. I don't want anyone able to change the overall schema of the databases. Let me know if you understand what I am trying to accomplish.
Thanks Again!!!
June 21, 2004 at 12:43 pm
Thanks for all the help. I think I got it.
The only question that I have is about the db_ddladmin role. Why do you suggest I add this to all the users. If I do add this to the database users will they be able to create new databases/tables etc. The only person I want to be able to create new databases/tables/etc. is myself.
I only want the developers to be able to write stored procs/views/functions, update/insert into tables etc. I don't want anyone able to change the overall schema of the databases. Let me know if you understand what I am trying to accomplish.
Thanks Again!!!
June 21, 2004 at 1:45 pm
Then I suggest that you may use GRANT statement that is more granular then ddladmin. You can look up the syntax in BooksOnline searching for the keyword GRANT. It will give you the ability to grant specific permissions like GRANT CREATE PROCEDURE TO YOUSQLUSER
Let me know if you need help with writing and executing statements. Please, try it first on the test database like Northwind or Pubs
Regards,Yelena Varsha
June 21, 2004 at 2:39 pm
I really do appreciate all of the help.
So far, this is what I've done.
Created to new user roles: Grant_StoredProc_Right & Grant_Table_Rights
These are the two stored procedures that I used to fill the permissions
===============================================================================
CREATE PROCEDURE sp_grant_permissions_proc
@role VARCHAR(100) = 'Grant_StoredProc_Rights'
AS
DECLARE curname CURSOR FOR
SELECT
name
, user_name(uid)
FROM
sysobjects
WHERE
xtype = 'P'
AND
category = 0 --type = user stored proc
DECLARE @procname varchar(100)
DECLARE @username varchar(100)
OPEN curname
FETCH NEXT FROM curname INTO @procname, @username
WHILE @@fetch_status = 0
BEGIN
DECLARE @sql VARCHAR(300)
SET @sql = 'grant execute on ' + @username + '.[' + @procname + '] to ' + @role
-- PRINT @sql
EXEC(@sql)
FETCH NEXT FROM curname INTO @procname, @username
END
CLOSE curname
DEALLOCATE curname
GO
===============================================================================
CREATE PROCEDURE sp_grant_permissions_tables
@role VARCHAR(100) = 'Grant_Table_Rights'
AS
DECLARE curname CURSOR FOR
SELECT
name
FROM
sysobjects
WHERE
xtype = 'U'
DECLARE @tablename varchar(100)
OPEN curname
FETCH NEXT FROM curname INTO @tablename
WHILE @@fetch_status = 0
BEGIN
DECLARE @sql VARCHAR(300)
SET @sql = 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' + @tablename + ' TO ' + @role
-- PRINT @sql
EXEC(@sql)
FETCH NEXT FROM curname INTO @tablename
END
CLOSE curname
DEALLOCATE curname
GO
===============================================================================
Next, I assigned the test developer user to both of these roles as well as the db_datawriter role.
As far as I can tell, everything is working properly. Do you see something wrong with the two queries above? Is there a better, more effecient way to do this?
Thanks for all your help
June 22, 2004 at 12:11 am
Yikes! Keep it simple!
SQL BOL lists:
| Fixed database role | Description |
|---|---|
| db_ddladmin | Can issue ALL DDL, but cannot issue GRANT, REVOKE, or DENY statements. |
| db_datareader | Can select all data from any user table in the database. |
| db_datawriter | Can modify any data in any user table in the database. |
If you give make your developer a member of these three roles, they will be able to SELECT, UPDATE, DELETE, INSERT, CREATE, ALTER, DROP anything in the database.
If you GRANT EXEC on all your sprocs to PUBLIC than anyone in the database can execute them (your sprocs don't do anything bad anyway right?).
Julian Kuiters
juliankuiters.id.au
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply