Deny all permissions
-
SQL Server is deny by default, so as long as the user is not tied to a sysadmin, you would add the user to the database, and then add that user to a role that has EXECUTE permissions;
if the user is a sysadmin, you cannot prevent their access to any database objects until you take away the sysadmin rights.
if you do not grant them permissions (and they do not add permissions from other roles that were granted), you get what you are after: no access.
adding a user and granting permissions are a three part process.
first you really needed to already have a ROLE in your specific database that has the permissions you want to give access to;
users are also a per-database object, that are tied to a login, so to do the same to a second database, you repeat the same steps(except if the login exists)
here's an example of creating a role,and giving that role Read only execute permission to stored procs and functions you created in that specific database.
USE [SandBox] --my db for users to do stuff.
CREATE ROLE [ExecuteOnly]
--give my new role permission to run the procedures you've created
GRANT EXECUTE TO [ExecuteOnly]
now that the role is created, we can add a LOGIN to the master database
IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')
BEGIN
EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'SandBox', @deflanguage = N'us_english'
END
now that a LOGIN exists, lets add a USER, tied to that login, to our database
--make a user in the db for the matching login
CREATE USER [bob] FOR LOGIN [bob]
finally, add our user bob to the role we created
EXEC sp_addrolemember N'ExecuteOnly', N'bob'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thank you very much!
-
Hello again
I cant use the login created from the above written procedures to access the database unless i make it sysadmin. If I make it sysadmin the login will be granted all the initially stripped permissions. This is becuase the I have set Integrated Security=True in the configuration file.
Any hint for the above dilemma?
Thank you
-
debbas88 (5/10/2011)
Hello againI cant use the login created from the above written procedures to access the database unless i make it sysadmin. If I make it sysadmin the login will be granted all the initially stripped permissions.
Any hint for the above dilemma?
Thank you
remember there is a difference between LOGIN and USER.
giving a LOGIN sysadmin rights overrides everything, so it is masking the USER issue altogether.
to avoid confusion, lets deal in specifics....what is the database name?
did you create the Role in that database, or did you accidentally create it in the master database?
what is the name of the specific user you are testing with?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
you still need to create a USER, even for windows logins.
for example, here's the script to add a windows user, and then tie him to the role we described:
--do i need to add the login on the server? probably already exists:
use [SandBox];
CREATE LOGIN [mydomain\lowell] FROM WINDOWS WITH DEFAULT_DATABASE=[WHATEVER];
--if i want to grant to a specific user:
CREATE USER [mydomain\lowell] FOR LOGIN [mydomain\lowell]
EXEC sp_addrolemember N'ExecuteOnly', N'[mydomain\lowell]';
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
This is what I have
Database: MainDB (lets say)
I have created a role in MainDB called ExecuteOnly which can only execute SPs
Login: Test\User1mapped to MainDB
User: TestUser for the Login:Test\User1
The Test\User1 is stripped from all possible server roles. The Test\User1 is given the role ExecuteOnly.
The application im using access the database through Test\User1. With the above restrictions, the application has no access to the MainDB. Note that the config file has Integrated Security=True in the connection string.
Thank you!
-
Integrated Security=True in the connection string.: that means that if myDomain\Karen uses your app, THAt is the username that will be used...NOT your test user.
you need to add a Windows group to the database...
i suspect it will be easier for you to use the GUI than a script; you need to KNOW the name of the windows group you want to add, or create a group in Active directory, add the right users to it, and then add that new group to SQL.
code wise, it's still basically the same, but you need to know the group:
CREATE LOGIN [NT AUTHORITY\Authenticated Users] FROM WINDOWS WITH DEFAULT_DATABASE=[WHATEVER]
CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users]
EXEC sp_addrolemember N'ReallyReadOnly', N'NT AUTHORITY\Authenticated Users'
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I understand a Windows group will act as a login. So once a windows group is created, if I strip it from all server roles, will my application be able to login to the DB?
-
debbas88 (5/10/2011)
I understand a Windows group will act as a login. So once a windows group is created, if I strip it from all server roles, will my application be able to login to the DB?yes, and you can test it:
after doing the above, do something like this:
EXECUTE AS USER = 'MyDomain\DomainGroup'
SELECT * FROM A_Table_I_Shouldnt_Access
REVERT; --change back to my regular , original login
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks a lot for your time, i will try it and see how it goes.
Viewing 10 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply