A question on the permissions hierarchy:
Since logins, database users, and database roles are both principals and securables - what does it mean to GRANT permission on a login/user/role to another principal? Does it mean that for a login - you can GRANT permission to EXECUTE AS that login or modify it, for example?
Hello,
I am new user of SQL Server. I have some problems with these words. I want to make my database works in my specified permissions. I will specify permissions with schemas and these schema wants an owner. I want this owner should be my user. When creating a user it needs a valid login. I am selecting my login and it occurs and error says this login has an different user. I am specifying permissions with roles. But i can't make association all of them. I hope i told my problem to you as well. If you explain these words to me and tell me how can i do my database's works with my own schemas, users and roles i'll be grateful. Thanks for advices.
Happy coding...
Sql 2005
I want to grate execute permissions on my stored procedures to a role. While creating the role, it asks for "schemas owned by this role".
To me, the schema is merely a namespace that allows you to group objects, but arent schemas such as db_datawriter roles that are central to the db and only admin type users should have ownership of these, correct ?
In a nutshell, I want to:
create a new role and assign a user to that role
with a stored procedure, grant execute permissions to this role
I was confused by the sql 2005 dialog that asks me to take ownership of roles such as db_datawriter, db_datareader etc, wouldnt that mess up other things with the database ?
help ...
I think its time I review all of the above items
role
user
login
schema
I am in need of a utility (stored procedure, third party app, etc)that will help streamline the process of managing and auditing objectpermissions, users and roles on multiple sql server 2000 instanceswith multiple databases on each instance. That's a quick overview ofwhat I need, do I need to be more specific?Thanks.
View 2 Replies View RelatedI have jsut started using SQL server 7 and am having problems with accounts permissions, users,roles, groups, owners etc what are the differences?
View 1 Replies View RelatedHello all,I am looking for the script, which I believe exists already.I need tobe able to populate the script for security of one database andapply it to another database, even if it is located on another server:1. All logins which not exist have to be created and which existsignored including the NT accounts2. Users same as the old database + the existing ones stay in database3. Passwords for the new logins.4. All permissions/grants on all objects for the users that exists(usually it's the case) and ignore those that don't.I have script which does some of it, but it's not perfect, so everytime there are some errors.Please let me know, if you need me to email script that I have. It'spretty long so I cannot just post it in here.Thank you in advance.
View 3 Replies View RelatedOur company has 2 Database Roles (DBE and DBA). The DBE creates
database schema, performs SQL Server Administration, and manages server
security. The DBA writes data access, ETL, and manages database
security. In 2005, we're struggling with how to allow the DBA to see
all of the logins on the server in order to add them as users of their
database. What permissions does the DBA need to select from any of the
logins on the server to add them to their database?
Michelle
Have a certificate and symmetric key that i have used the following to GRANT to logins. How can I find out which SQL logins have the GRANT CONTROL and GRANT VIEW DEFINTION?
GRANT VIEW DEFINITION ON SYMMETRIC KEY:: Symetric1 TO Brenda
GRANT CONTROL ON CERTIFICATE:: Certificate1 to Brenda
Hi all,
Can I grant select only permission on all objects in the database? I have users that I need to give view access only on stored procedures, triggers, and functions. Thanks.
I am trying to write a SQL script that grants execute permissions on all stored procedures for a single user (test_user).
I am running SQL server 2005
Does anyone have a script to do this.
Many thanks
We log ship our production database tansaction logs to a secondary database on a separate remote domain.
Incidentally, this was not set up via the "Ship Transaction Logs" wizard, but instead we had to use the various system stored procedures after restoring a copy of the database on the remote domain in standby mode.
We now have an issue where users are unable to access the remote secondary database without having sysadmin privileges. Clearly we want to avoid this!
We want to be able to use Windows Authentication mode on the server of the secondary database. But since the two domains differ, is it possible to even achieve this?
How can we set up read-only access for users on the secondary database without giving them sysadmin privileges?
Is there a script or can I make a script to find out what roles and what users are assigned to the roles are?
View 4 Replies View RelatedHow to generate script which can move users with his roles from one DB to another (SQL 2005) ?
View 1 Replies View RelatedA database move was accomplished by detaching the database from the legacy server and attaching it to the new server. The servers ran NT 4, SP6a, SQL 7 SP2. The new server had the same name and IP as the old server.
However that process (detach attach) does not bring over the users (who had access to the database on the legacy systems) correctly.
Under databases, roles the users existed. Under security, logins they did not.
We were obliged to drop the roles and recreate the logins and assign new roles. In the case of an NT user group we were unable to drop it. We had to create a new one and add the users to that group under User Manager.
So far I have not found anything that deals with the user account/role problem.
We have to move a similar database tonight. Do any of you know of an alternative "move" that brings the user roles/logins across correctly?
Thanks in advance.
It seems that SQL7 has lost the "Grant All" option previously available in SQL 6.5. Is anybody aware of an equiviant in SQL7?
Any help would be gratefully received.
Hello
Is it possible to grant permissions like SELECT,INSERT,DELETE permissions to a database in SQL Server 2005 as we give it through SQL Server Management Studio.
Is it possible to grant permissions without specifying the username,passwd
Thanks In Advance
Poornima
I'm trying to find some documentation or procedures that will allow me to xfer existing users' roles, etc from sql2000 to sql2005.
When our sql 2005 database was restored on a new server (from sql2000), all of the database users were orphaned. I need to create identical server logins and then make the logins database users. It is also important to understand the significance of the new schema concept. The Microsoft SQL2005UpgradeTechRef.doc white paper does not explain any of this.
Ultimately, I would like to create a script for the above scenario once a sql2005 db is restored from a sql2000 db.
Could someone please help me out on this?
Guys,
I have 2 users emp_rw (read write) and emp_ro in the database through which application accesses the database.
Now I would like to replace this users with roles and assign emp_rw, emp_ro to roles. Which roles should be created -
database roles or application roles what is the difference between application and database roles.
currently I execute the below statement to grant rights on database objects to emp and emp_ro users, can this be changed
to grant rights to role instead of user.
grant execute on <function name, procedure name> to emp_ro
Any suggestions and inputs would help.
Thanks
Hi, all. I've got a question that's truly newbie. I've added logins to the "users" of a database, but I've never used the "roles" area. What's the difference? Is a role like a group account with some preset permissions?
View 3 Replies View RelatedI'm trying to find some documentation or procedures that will allow me to xfer existing users' roles, etc from sql2000 to sql2005.
When our sql 2005 database was restored on a new server (from sql2000), all of the database users were orphaned. I need to create identical server logins and then make the logins database users. It is also important to understand the significance of the new schema concept. The Microsoft SQL2005UpgradeTechRef.doc white paper does not explain any of this.
Ultimately, I would like to create a script for the above scenario once a sql2005 db is restored from a sql2000 db.
Could someone please help me out on this?
Hello together,
can anybody help me. I'm looking for an easy way to grant permissions to a user in all user databases. I already have a script which grants permission to all views and userdefined tables within one database, but since I have to run it in about 100 databases it's still quite timeconsuming.
Is there a way to execute that script in all user databases at once ???
Markus
I have a larger stored procedure that is running, but I am getting stuck on where I need to grant permissions to a user in a a different database on various functions and stored procedures. For example:
Code Snippet
use [Database1]
grant exec on [Database2].[dbo].[MyFunction] to bob
returns this error: Cannot find the user 'bob' , because it does not exist or you do not have permission.
However, I know 'bob' exists, plus when I change the use statement to Database2, the line of SQL works correctly. Given the nature of the overall stored procedure this will be running in, I won't have the ability to just change the use statement. Is it possible to grant permissions to a user on a different database without explicity having the use statement set to a particular database?
Thanks for any advice!
-Flea#
Whats the difference between db_owner and public role...
I want to create a role and add user to it..that user will have all the permissions on all the tables (our tables), all permissions on all stored procedures, triggers, views...that user is owner of all objects....right now we have to manually grant these permissions to each user, and I was think if I create a role I don't have to what happens with stored procedures and jobs ??
Can some one help me the script to do this... am reading lots of articles about these but none have clear instructions...
How do you create
db_datareader, db_datawriter roles ??
I cannot use EM as I have to send this script with our application..
Thanks
Sonali
I have a number of jobs that will need to be run in an adhoc fashion(no set schedule).
I will be utilizing a mainframe operations staff and would like to limit their access on SQLServer
as much as possible while allowing them start jobs and view history. What would be the best way to go about this?
Thanks in advance.
Hi,
How to set permissions for users to run or edit packages?
How to notify the changes made to packages and where to find the change notifications?
We setup a number of roles with access rights to tables in the DB. This week for some unknown reason, rights on these roles disappeared.
We had to run a restore to reset the roles in the database. After the restore, we could not reproduce the problem.
Are there scenarios to avoid that would cause rights to drop from roles and users? (These rights were gone not just hidden)
Tim.
I am trying to come up with a stored procedure to grant all usersdb_owner role (rather than have to manually check the users eachtime)Thanks in advance for your helpTY
View 1 Replies View RelatedThere's something I can't quite figure out about user creating
The application that I'm currently working on is interacting with DB, therefore every time you use application you need to login as user which is fine. The problem is that certain users should be able to create new users and the new user may even have the same level of permissions as the one that's creating it ( like admin creating another admin acount or some like that).
Question is how can I allow users to create these user with giving them as few permissions as possible.
If there's is somewhere a code sample on the net I would appreciate the link.
Hi everyone out there in ASP.NET land.
Have a quick question...
How do I add a role and/or a user through code (Specifically SQL Statements). If not through SQL, then maybe VB.NET?
Thanks ahead of time,
Denvas
Hi there NT Server,sqlserver 7.0, Integrated Security. I used "sp_attach_db" to attach a database to the sql server. The database has 3 standard roles with members in each of those roles. But i am not able to add a user to any of the roles through Enterprise Manager(No users are listed, except the other 2 roles). Also when i click "users" under the Database in EM, no users are listed except "dbo" with sa login. Luckily since some members are there in the roles, they are able to connect to the database. Also i am not able to add the user bcos the user exists in the public role and i get an error message "user already exists in DB". The user has logged into the NT and everything is fine. I copied the .mdf and .ldf files from a "Standard Security" Server.
View 1 Replies View RelatedSHORT VERSION
i am setting up roles, do i also have to set up role-people as users?
LONG VERSION
my db has two owners. both of us run the Access front end happily - everything works perfectly.
now i'm trying to add non-owner users.
i have set up ten people as datareader/datawriter (primitive, but will refine this later).
these people are not able to get into the db.
at the registering DSN stage they can't see the server in the list of servers and if they manually type \myserver they can't connect (doesn't exist or access denied).
in Enterprise Manager,
Users: shows only the two owners
Roles:datareader shows the ten non-owners (ditto datawriter)
am i supposed to setup my datareader/writers as users as well?
should i see them in the Users: list?
thanks for your time.
izy
How can I generate a list/view of all users/groups with appropriate roles and rigths?
View 3 Replies View RelatedHi There
The backup database permission seems to only be at a database level , grant backup database and db_backupoperator role only exist at a database level.
However i want to permission to a user to backup any database, and he must not be a sysadmin. I cannot find a server level role that can backup databases.
How can i do this? There are alot of database so granting the permission for every database seems a waste of time, also one would have to remember every time a new database is created to grant this permission.
There must be a way to do this at a server level?
Thanx