Role Permissions
Apr 11, 2008Hi Guys,
How to copy one user role permissions to another role in same databse using stored procedue(sp)? Can any one help how to write sp.
Thanks.
Hi Guys,
How to copy one user role permissions to another role in same databse using stored procedue(sp)? Can any one help how to write sp.
Thanks.
Where can I find the information about dbcc setcpuweight? I checked books online and couldn't find it. I also went and searched Google with the same results. Any help is greatly appreciated.
View 2 Replies View RelatedI created a new database role to give a number of users select privilege only on some tables and every day I have to go back in and add the tables back to the role. Is there something I'm missing here?
View 8 Replies View RelatedCan we assign serverrole to a database role.
For example i have a new role created as A
Now can i add A to any of the server role..
Please advice..
Thanks,
ServerTeam
Hello.
I've moved a database from 2000 to 2005 and in 2005 I cannot see the permissions for this Database Role that I created. In 2000 you just right-clicked, selected properies and clicked on permissions. Am I going mad here or is this not an option in 2005. If not, how do I see the permissions that have been given to the role?
The 2005 database I refer to is on standard edition SP2
Thank You
thanks for reading.
i would like to create a new database role that has exactly the same permissions as an existing database role. the combinations of permissions are complicated enough to make this a time consuming task worthy of a script of some sort. any suggestions?
i'm running SQL Server 2000 and can't find anything about copying roles unless it's through DTS from one DB to another. not sure if this is applicable to basically duplicating and renaming a role in one database.
in case you're wondering why anyone would want to do this you may not be surprised to know that it's a government thing. some policy about differentiating between 2 roles -- even though they currently have exactly the same permissions, the similarities might change in the future.
thanks in advance for your input.
Brief description of the problem:My production server has about 50 databases and various permissions aregranted to public role on all these databases. Because of this any newuser added to any database gets unnecessary access to objects by virtueof being a member of public by default. I would like to fix this flawin the way the server is setup as below:1. Setup a new database role called NewRole on all 50 databases andcopy permissions to NewRole from public.2. Add all existing users to the new Role.3. Remove all permissions from public.Any suggestions on scripting this task are welcome.
View 3 Replies View RelatedCan someone tell me what permissions has public role in sql server? can it read every table or it can just connect to a database and no more?
thanks
I want to allow administrators of the program to change the permissions of the database roles. Is there a way to retreive the the specific permissions granted an denyed to a user and a database role. For instance, a store procedure that you call, pass it the database role and you get whether SELECT is allowed on table1, or if UPDATE is denied on table2, etc.
Is there a way to script out a database role from SQL management studio?
I can only get a script for create and drop. I am looking for a script that shows all object permissions that the role has in a database.
Thanks.
I am not a DBA so please be gentle...
I am trying to export all of the user and role permissions out of several databases for auditing purposes. I see the Users and Roles listed under the Security tree view when I log into the database, but I do not see an option to export or query the permissions. In addition, we do not have any tables that reference user permissions in our databases. So, how would one go about exporting or querying this information?
I've seen similar topics where they recommend querying sys tables to gather the info, but I don't see those tables either. Any help would be greatly appreciated.
All my thanks!
- Isaac
Edit: I should add in that I am connecting to 7 and 2k DBs using 2k5 SMS. Not sure if that makes a difference...
Every night, there are some stored procedures that run to recreate tables so that the information in the table is updated. After the tables are droped and recreated I have to go in and check the select box under the permissions for the public role. If i don't do this users will not be able to select from theres tables.
What can I do so that users are able to select from these tables after they are created?
Would you be able to specify the select permissions for the public role in the script that creates the table or run a script that gives all those tables select permissions for the public role?
All help is appreciated.
Hi guys,
I'd appreciate your thoughts on this. Not done too much DB admin. Usually I do development work and the admins to the admin.
The database is behind an API of stored procedures to manipulate the data, and views to select the data.
The database needs to be accessed remotely by multiple clients.
How best to keep the database secure?
Create a new user and login on the database which is made known to all client applications. Then grant execute permission on the stored procs and grant select on the views?
There is probably a better way than one login for all? Should I be looking at roles and groups etc? If so, how best to set that up?
A few pointers would be gratefully received!
We have an audit issue that is requiring me to revoke the select permissions from the public role to the system tables. Has anyone had to do this?? What problems did you experience? Are their any tables that you were not able to change the permissions on? Any help is greatly appreciated.
View 5 Replies View RelatedHello,
Does anybody have the transact SQL to find the permissions granted to the public role in a Database ?
Also looking for the SQL to find the permissions granted to the user Guest in a database ?
thanks
I have a stored procedure in which at the bottom of the code, im granting execute permissions to a role I have defined. However, when I view the permissions on the procedure, the role isnt there, what could I be missing ? The procedures were all created under the default or dbo schema. I could manually give the permissions to the role, but id rather have it scripted.
help ?
How would I, using a sql script, copy permissions assigned to a useror a role in one or more databases to another user or a role in theirrespective databases?Help appreciated
View 2 Replies View RelatedIve been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
thanks
In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?
View 1 Replies View Related Hi all, I am trying to connect to the database using application role. But gives an error An error has occurred while establishing a connection to the
server. When connecting to SQL Server 2005, this failure may be caused by
the fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)
for the given connection string Dim connstring
As String = "Data Source=Northwind;Initial
Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"
Dim cmd
As SqlCommand
Dim
param As SqlParameter
Dim
cookie As Byte()
Dim cn As New
SqlConnection(connstring)
If
(cn.State = ConnectionState.Closed) Then
cn.Open()
End If Please help..
Thanking you, Nirmala
Using SQL Server 2k5 sp1, Is there a way to deny users access to a specific column in a table and deny that same column to all stored procedures and views that use that column? I have a password field in a database in which I do not want anyone to have select permissions on (except one user). I denied access in the table itself, however the views still allow for the user to select that password. I know I can go through and set this on a view by view basis, but I am looking for something a little more global.
View 5 Replies View RelatedHi,
I have SQL server 2005 (Developer edition) installed and I want to add a database role to a database role. It is working on my SQL 2000 server,however, when I tried to do the same thing on SQL2005, It didn't allowed me.
When I go to the Add role and then adding members to the role, the browse screen does not allow me the choice of object type Roles. It only shows me "Users".
Can someone please help me with this and provide me some information of how / what should I set to get the Roles in object types list so I can add a role to a role.
Thanks
Hi , I'm looking for a way that SQL-S7 tells me, what db-role I have.
I want to avoid that the server generates an error message when I'm not
authorized to access an information. So I could give the users only that
information they are authorized for.
May be there is a stored procedure or a template ?
Or is there an other way for controlled checking whether I have the rights
or not ?
Thanks for help
When I assign a role to a user, the user is not actually getting the permissions defined in the role. I am forced to give the permissions directly to the user..any idea why it's ignoring the role which is assigned?
Thanks.
I have set up all new Roles assigned different views, table access to each user i set up.
Whilst setting this up - i could do script to generate the script.
Is there a way to get this script after the effect - some roles i forgot to generate script.
I know im being lazy - i could write the entire script out but thought maybe there a way to get this .
Thanks
Hello:
I have read that giving a User the DB_DDLAdmin role in SQL might causes problems with ownership chains in the future. Since the User will have ownership to all objects created, what preventive measures can one take to help avoid any problems which might loom in the distant future due to ownership chains?
Thank you,
-H
I am new at this and we encountered a problem. Can names in the public role be deleted? We have some names that need to go - however the delete option does not high light?
I would appreciate your help..
Hi Everybody,
Experts........
i am using Sql server7.0
I want to Delete existing Role How Can I Delete it.
Anybody can please Tell me Immediately...
I tryied Sp_DropRole But it display's message successfully
completed but role of that name is still there...
Thanx a Lot in Advance.....(after solving this problem)
(Mohanlal)
Hi everybody,
The below I posted on SQL 2000 Forum about a week ago.
Any new thoughts................
I would like to get an input from as many people as possible on the following:
In our organization DBA is responsible for 5 servers ( currently NT 4/SQL 7)and is a part of group of a 5 people including manager and 3 developers.
DBA currently has a FULL access to every server.
In a few months we will be replacing the existing system with Windows 2000/SQL 2000.
LAN group will give to DBA only a read rights for the Windows 2000 environment, saying that the AUDITORS, both internal and external, require that. In other words, if DBA needs to run a command prompt, move files from one directory to another in Production environment, he has to fill the request to LAN, so LAN group would do that.
So I guess the main question(s) is:
What is the degree of involmment of DBA with Operating system?
Is DBA suppose to be an NT administrator ( I dont think so, since DBA has a lot of other thing to do?
If DBA accidently makes an unwanted changes to the Operating System, who should be blamed for ( not personally, but in more general terms) and would it be an extra argument to take write rights away from a DBA?
What auditors saying about that?
Thanks a lot in advance,
Andrei
Hi Everybody,
The end users are using VB Applications, there they will be entering datas. Those datas will be stored in the SMS Database.
My Problem is through which Roles (Fixed Server Roles or Database Roles) I should attach these end users. If it is a Fixed Server Roles, Other than sysadmin role in which role I should attach this end user. Like that other than DB_Owner in which role I should attach these end users to the Database Roles.
Can anyone guide me please.
thanks,
Srinivasan.
Hi!
What server role should be assign for individual to create and execute DTS packages.
Thank you,
Elena.
Hi All,
Question:
Why would the user other than sa need the db_owner rights?
Hi everybody,
I would like to get an input from as many people as possible on the following:
In our organization DBA is responsible for 5 servers ( currently NT 4/SQL 7)and is a part of group of a 5 people including manager and 3 developers.
DBA currently has a FULL access to every server.
In a few months we will be replacing the existing system with Windows 2000/SQL 2000.
LAN group will give to DBA only a read rights for the Windows 2000 environment, saying that the AUDITORS, both internal and external, require that. In other words, if DBA needs to run a command prompt, move files from one directory to another in Production environment, he has to fill the request to LAN, so LAN group would do that.
So I guess the main question(s) is:
What is the degree of involmment of DBA with Operating system?
Is DBA suppose to be an NT administrator ( I dont think so, since DBA has a lot of other thing to do?
If DBA accidently makes an unwanted changes to the Operating System, who should be blamed for ( not personally, but in more general terms) and would it be an extra argument to take write rights away from a DBA?
What auditors saying about that?
Thanks a lot in advance,
Andrei