Create groups for an application

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

Create groups for an application

Author

Topic

greaseman
Starting Member

30 Posts

Posted - 2006-06-01 : 12:00:13

I have a VB application using an SQL server database. I've created two groups in SQL Server for the database, one called APPS Users and the other called APPS Admin. So far, so good. However, when I place users into the APPS Admin group, it seems that besides getting to my application via a connection string, they also get complete access to my SQL Server. I don't want them to have total access to my SQL Server, only to the application's database.

In short, I want to limit them to nothing more than my application and its related database. In my application, these APPS Admin users will see a different group of menus than the regular users.

Since some of these APPS Admin users are also DBO's, how can I set up my group to limit them?

Hope I made my plight clear.... any help offered is most appreciated! Thank you!

Wherever you go, there you are!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-01 : 12:15:42

>> I've created two groups in SQL Server for the database ?
Means
two Users ?
two Roles ?

Do u want to create 2 users one has admin rights to the SQL server, while dealing with the DB and other to deal with the DB only ?

Srinika

greaseman
Starting Member

30 Posts

Posted - 2006-06-01 : 12:27:54

I mean that I created two groups, one group having users with no admin rights, and the other group having users with admin rights.

> Do u want to create 2 users one has admin rights to the SQL server, while dealing with the DB and other to deal with the DB only ?
In response to your second question, yes, something like that. However, more like the second portion..... to deal with the DB only, but within the DB, to have supervisor rights in order to be able to use a different set of menu options. However, some of these "super users" are also DBO's, and that's where my plight arises.

Hopefully, you can help shed some light on this. In any case, thanks for responding. I look forward to your reply.

Wherever you go, there you are!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-01 : 12:34:37

By the following u can find the Users groups, rights etc.

create table #Users
(
UserName varchar(200),
GroupName varchar(200),
LoginName varchar(200),
DefDBName varchar(200),
UserId int,
SID varbinary(500)
)
go
insert into #Users EXEC sp_helpuser
select * from #Users
Drop table #Users

Or u may read about xp_logininfo in BOL

& U may read the following in BOL (In the Index : GRANT, GRANT (described))

Granting Permissions
DENY
REVOKE
sp_addgroup
sp_addlogin
sp_adduser
sp_changegroup
sp_changedbowner
sp_dropgroup
sp_dropuser
sp_helpgroup
sp_helprotect
sp_helpuser

Srinika

greaseman
Starting Member

30 Posts

Posted - 2006-06-01 : 13:04:58

I don't understand your reply.....yes, that might show me my users, groups and so forth, but then what?

My question was: "when I place users into the APPS Admin group, it seems that besides getting to my application via a connection string, they also get complete access to my SQL Server. I don't want them to have total access to my SQL Server, only to the application's database." This is the part I don't understand how to do.

So listing out users and groups is fine, but what does that do for my question?

Thanks for responding!

Wherever you go, there you are!

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-06-01 : 13:21:05

>> So listing out users and groups ...
So that u know whether which group has admin rights or what

Then u have to refer the BOL on things like

Granting Permissions
DENY
REVOKE

and those have examples how to revoke the Permissions (in ur case Server Access)

Also if u look in Enterprise Manager
U'll be able to find the Users / Rights etc (In Security the Users to the SQL server + DBs, and in individual DB the access for each DB)

Srinika

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-01 : 13:38:55

Use the IS_MEMBER function to see if they are in a role, and if they are, display the menu items for that role.

if IS_MEMBER('APPS Admin') = 1
   print 'Current user is a member of the APPS Admin role'

You can't limit what a DBO can access in a database, so don't put them in the db_owner role.

If they have complete access to your SQL Server, you must have put them in the server admin fixed server role. Don't do that either.

CODO ERGO SUM

greaseman
Starting Member

30 Posts

Posted - 2006-06-01 : 15:40:24

Ok.... thanks for the responses! Man, this stuff is enough to geive headaches! Wish there was somewhere that had the do's and odn'ts in somewhat of a simplistic style, for all us noobies!

I appreciate your replies!

Wherever you go, there you are!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-01 : 15:57:17

You should read the "Managing Security" topics in SQL Server Books Online.

CODO ERGO SUM

greaseman
Starting Member

30 Posts

Posted - 2006-06-01 : 16:52:53

Thanks! Like I said, I am definitely a nnobie. These "SQL Server Books Online" - are those the ones in SQL Server, that I've loaded onto my PC? I know it's a beginner question, but it's the only way (sometimes) to find something out.

I appreciate your replies.

This forum is best used with a computer. Questions asked freel.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-01 : 17:39:09

If you have installed the SQL Server client tools on your PC, you should see "Books Online" on your MS SQL Server menu (in SQL 2000).

They are also available online:

SQL Server 2000 Books Online
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/startsql/portal_7ap1.asp

SQL Server 2005 Books Online
http://msdn2.microsoft.com/en-us/library/ms130214.aspx

CODO ERGO SUM

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-01 : 21:07:34

well you can however use an application role

when the application is ready for deployment, change the connection string they use in the development to use an application role

which is similar to controlling the connection credentials

in my previous engagement, I ask the project manager to change the connection credentials to a new one, which I supply, and have him create the deployment package

he is very cooperative as he understands the need to protect the live database, also I emphasize that if something happens to the live database, then he is held accountable, if the developers share the credentials, they are held responsible... and believe me when I say I hold them responsible

--------------------
keeping it simple...

greaseman
Starting Member

30 Posts

Posted - 2006-06-02 : 08:56:43

And again, to all who have responded, a big thank you! I started looking into application roles, but that was at the end of my work day. I'll check it out more today.

Another question or two. What's better to use.... Windows NT certification or SQL Server authentication? When I used Windows NT certification, my application (which has a login screen), "blew by" its login screen. This part of the application, written by others, apparently takes the SQL dbo name and does nothing with the user I created in SQL Server. How do I get around a headache like that?

I appreciate y'all for helping out a confused / frustrated person.

This forum is best viewed with a computer. Questions asked freely. Confusion happily shared.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-02 : 11:59:54

change the sql dbo password?

dev: hey, my apps is not working anymore
you: why not? what's the error message?
dev: it says, the dbouser does not have permission anymore?
you: permission to do what?
dev: [to create tables]... just select on tables
you: but the user is db_datareader member, meaning it can read all tables in the database
dev: oh ok... but it worked before, what changed?
you: it should work fine with your requirements, unless you're doing something it shouldn't be doing?

Better yet, to avoid things like this, create the sprocs and udfs and I'll even help you design them. This way when you want to change the business logic you can do so without changing your exe

yada yada yada....

--------------------
keeping it simple...

greaseman
Starting Member

30 Posts

Posted - 2006-06-02 : 12:12:20

jen,

thanks! I was thinking of changing the dbo password, actually.

I didn't quite understand the last part of your reply.....

>dev: hey, my apps is not working anymore
>you: why not? what's the error message?
>dev: it says, the dbouser does not have permission anymore?
>you: permission to do what?
>dev: [to create tables]... just select on tables
>you: but the user is db_datareader member, meaning it can read all >tables in the database
>dev: oh ok... but it worked before, what changed?
>you: it should work fine with your requirements, unless you're doing >something it shouldn't be doing? Better yet, to avoid things like >this, create the sprocs and udfs and I'll even help you design them. >This way when you want to change the business logic you can do so >without changing your exe yada yada yada....

Can you explain what you are trying to tell me?

This forum is best viewed with a computer. Questions asked freely. Confusion happily shared.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-06-02 : 12:17:19

lol... just be prepared for those "violent" reactions...

but it would help you ease the tension by preparing a "plan" first, emailing/informing those who will be affected, just some diplomatic stuff to make it official

--------------------
keeping it simple...

greaseman
Starting Member

30 Posts

Posted - 2006-06-02 : 13:09:16

Ah!! The lightbulb just went off! In other words, if I change the password from the dbo, since that's what they have probably been used to, I should put my head between my knees! Got it!

Actually, what I had kinda sorta thought of using was a small table with ids and encrypted passwords that my app would have to pass through, however, my immediate boss said an ini should be used instead. IMO, isn't an ini a type of a table? Wht's the difference? Oh, well.

I still like the idea of changing the dbo password. Now, off to convince others!

This forum is best viewed with a computer. Questions asked freely. Confusion happily shared.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-02 : 14:36:00

quote:
Originally posted by greaseman
...I still like the idea of changing the dbo password. Now, off to convince others!...

Don't ask, just do it.

It's always easier to get forgiveness than permission.

CODO ERGO SUM

greaseman
Starting Member

30 Posts

Posted - 2006-06-02 : 16:10:11

Wish it was that easy. If I controled my paycheck, I would, but since I don't, I won't and can't.

Question: In code, you can use something like "If User Name = 'dbo'...."

Is there an equivalent for checking a group name, or does a group have a user name?

This forum is best viewed with a computer. Questions asked freely. Confusion happily shared.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources