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.
| Author |
Topic |
|
mparter
Yak Posting Veteran
86 Posts |
 Posted - 2002-03-04 : 11:21:24
|
| I have my tables on a server running SQL Server 2000 and the front-end in an Access 97 database. I have setup some user accounts in SQL Server for the staff to access the database. Some of these are read only.How do I capture the users permissions when opening a form so that I can lock it using the AllowEdits function if the logged in users account is read only?Or is there a better way to handle this cause my forms still allow data editing to be done even if the account is read-only. It generates an error when the user tries to move to the next record.Thanks |
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-04 : 19:34:51
|
without going into all the details, try thisselect o.name, u.name, select_allowed = case when (1 & p.actadd) = 0 then 0 else 1 end,update_allowed = case when (2 & p.actadd) = 0 then 0 else 1 end,insert_allowed = case when (8 & p.actadd) = 0 then 0 else 1 end,delete_allowed = case when (16 & p.actadd) = 0 then 0 else 1 endfrom syspermissions p inner join sysobjects o on p.id = o.idinner join sysusers u on p.grantee = u.uidwhere o.name = @sTableNameYou could do something similar if you want to go by the inverse - ie actions denied - just change p.actadd to p.actmodYou can also modify that slightly if you want to do group security rather than user level....Enjoy Edited by - rrb on 03/04/2002 19:49:47 |
 |
|
|
mparter
Yak Posting Veteran
86 Posts |
Posted - 2002-03-05 : 04:11:16
|
 Don't know where to even begin with this one! I'll try it anyway!! Cheers :) |
|
|
|
smccreadie
Aged Yak Warrior
505 Posts |
Posted - 2002-03-05 : 07:13:22
|
| You can also manage the security in access. Probably more work to set up but it's an option. |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-05 : 16:59:49
|
| You are allowed to ask questions - it's a new rule...What don't you understand?--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
mparter
Yak Posting Veteran
86 Posts |
Posted - 2002-03-06 : 04:22:20
|
Thanks rrb.Basically when you access any table it asks for the SQL username and password. I have several different accounts setup on SQL server, some of them have write access, some of them only have read access.When the user enters their username and password I want to be able to find out what kind of permission they have and then use this to determine whether or not to lock the form for read-only access.Do you follow me?Don't want to use the Access way of security cause that means I can't administer accounts remotely plus the fact it's too clunky!!Thanks for your help  |
|
|
|
rrb
SQLTeam Poet Laureate
1479 Posts |
Posted - 2002-03-06 : 17:03:24
|
quote:
Do you follow me?
Where-ever you lead....If you take my code, add the line and u.name = 'mparter'then the query will return exactly what permissions you have on @sTablenameEverything (well almost everything) in SQL Server is held in tables. If you have a look at the code, it's only a query - just that you're querying the system tables to find out the permissions.Once the user has logged in, you can run the query to find out whether they have update_allowed set for the current table, if not, iterate through all the controls and lock them. etc etc.Hope that makes it clearer.But please ask, if I'm still not making sense.--I hope that when I die someone will say of me "That guy sure owed me a lot of money" |
|
|
|
|
|
|
|
|
SQL Server, Access Forms and Read-Only